Posts by M40wen

    Hi Kenneth

    so the process works as follows (it is for a builder who has multiple sites and plots).

    The accountant spreadsheet gets dumped onto an input sheet
    User presses a button to clean and then add into a data sheet automatically
    if there is a new supplier, it adds this to a company sheet, and colours in the data sheet so the user will know it needs to fill in
    when returning to the data sheet, there should be a drop down list only showing what is in the corresponding drop down

    it all works well enough. However the user might want to change the set up, and hence why it needs to be sorted. My solution for this was to flag if the company or activity sheet is sorted, then to add back validations for the entire datasheet. It seemed overkill, but we are only talking a few thousand rows so better to be safe.

    interesting that you said sorting the columns left to right works for you, it doesnt for me. The project endedup a few thousand lines of code so another dozen wasnt too arduous!

    Did you set a named range and used that?

    Apologies I had trouble logging in and the site is very slow.

    I recoded it to use named ranges - and found that it still does the same thing! It looks like if anything gets added to my set up table and then sorted alphabetically left to right, I will have to redo all the validations for all affected cells in the data sheet using those validation lists. Im also tempted t take out the named ranges again as I prefer to code in the cell refs. This really needs fixing by MS.

    I guess the best way is to flag if something is going to be added (my code checks it is on the setup company or activity table), then if so not to do the validations as its importing in, but to wait till the end then rerun for all.

    Hi all,

    I currently have a list of companies in the first row, and activities underneath for each company. This is then used as a data validation for cells. However, if I add a company in, then sort left to right, the existing data validation doesn't update with the new sorted address... Do I need to recode and update every drop down if I do a sort? Reason being my client likes to look at the company activities and wishes to view it (my code just added on end then sorted - but now causes this problem). I would say this is a bug that needs fixing because if you are sorting the "validation list", then the validations should update accordingly. Have I missed a trick here? I thought that by removing the $ the validation would work but it doesn't - though if I add or remove a column the validation works correctly (so why not sort MS)

    Any help or ideas?


    Im having trouble with this formula

    =INDIRECT("'"&B5&"'!C"&MATCH("Total Actual Retail/Revenue.",'2221866'!B:B,0)&"")-D5

    I just want the '2221866' To be referenced to Cell B5 (like at the start), but I cant seem to get the right notation. Can someone urgently help me?

    Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8

    Ahh I see - you aren't changing anything in the column, its the data value that's changing. QQ:-

    Is column C sorted already?

    Do you have headers?

    Does data start in row 2?

    What is the name of the sheet this process is on (I prefer to ref the sheet directly that's all)

    For the data, which column is the last data set in? Will it always be populated or is there blank details (im trying to identify what your last row of your data range is in)

    Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8

    This need to go in the sheet code

    Re: Reorder Columns Based on Headers

    I think you may find this approach more helpful - you've expired on the goodwill with me!

    Re: scan and copy top 10 values using vba

    A bit quick and dirty but... You may need to adjust the range if your data is actually bigger than two columns otherwise you will be sorting a partial data set. Let me know if im a legend

    Re: Random Slow Down Of Text Import

    Ok so after MUCH head scratching....

    This macro is part of 5 files that get imported (the code is more or less the same with just a few tweaks). The issue is the parsing text to columns - now I have no idea why its an issue but can only assume something is getting held in memory which randomly slows down the next file (and again it is random). So ive given up on trying to understand why, but what I have found is if you remove the text to columns, and instead import all the text files THEN run the text to columns together, I get NO slowdown, and the files are consistently running (in my case under 3 mins for 10 files/sheets in total over around 1.5 million rows). I don't understand why running the macro a second time has no slowdown, so if anyone can "explain" that to me id be grateful, but for now ive tested this out over a dozen times and we are looking good ... Hope this helps someone else.

    Re: Reorder Columns Based on Headers

    Re: Reorder Columns Based on Headers

    Amend code above, basically it will only copy it if it finds it, else it finds next one..

    1. For i = 1 To lc
    2. on error resume next
    3. Fnd = 0
    4. fnd = Sheet2.Rows(1).Find(Ar(1, i)).Column
    5. on error goto 0
    6. If Fnd <> 0 Then
    7. Sheet2.Columns(fnd).Copy Sheet3.Cells(1, i)
    8. end if
    9. Next I

    Re: Reorder Columns Based on Headers

    For i = 1 To lc
    on error resume next
    Fnd = 0
    fnd = Sheet2.Rows(1).Find(Ar(1, i)).Column
    on error goto 0
    If Fnd <> 0 Then
    Sheet2.Columns(fnd).Copy Sheet3.Cells(1, i)
    end if
    Next i