Conditional Macro to pull No Match Customer Names

  • Hi All,


    I had a macro decide to stop working today, so I've spent the day rewriting it and am in the testing phase. For the life of me (probably because I've been staring at it all day), I can't figure out why my macro is hanging at the following point in underlined red (see code pasted below). The goal of this macro is to compare a text file that I receive from my sales broker each morning with customer account sales in it and compare to my customer name spreadsheet BEFORE I import it into my accounting software, then identify those customer names that don't match my customer names in my accounting software. My accounting software is the master file for my customer name spreadsheet and is updated monthly. The macro is embedded in the customer name spreadsheet. A fresh set of eyes to look at this macro and tell me what I'm missing would be welcome because I can't get this blinkety-blink-blink thing to work.


    Thanks


    Karen


    Karen Stone
    Staff Accountant
    ZD Wines

  • Re: Conditional Macro to pull No Match Customer Names


    I don't see anything underlined in red, but I found a note on this line.

    Code
    vDataOne = .Range("f1:f" & .Range("f65536").End(x1Up).Row) ' error on this line


    It should be xlup, the second character is the letter "l" as in larry, you have the number (1) one.


    I think there are several of those.

  • Re: Conditional Macro to pull No Match Customer Names


    So I've made the corrections that I missed on Friday and this is probably because it's been so long since I had to rewrite a macro, but I'm still getting a VBA error 424: object required at the same line. Have I written my range declaration incorrectly? The idea is for the macro to search in column f for customer names then match them to the QB customer list and, as the comment notes, flag them without a match in a separate tab. I have my original notes from when the original macro was written in 2009 so I'm assuming the code may have changed somewhat in the following 7 years, possibly making me a newbie again.


    What am I missing? I've edited the line as follows:


    vDataOne = .Range("f1:f" & .Range("f1500").End(xlUp).Row)


    Any inputs/corrections would be most appreciated!

    Karen Stone
    Staff Accountant
    ZD Wines

  • Re: Conditional Macro to pull No Match Customer Names


    It's the .select.


    You don't need .select when using the with statement.


    If you want it to be a range you should declare it as a range and use:

    Code
    Dim vDataOne As Range
        With Sheets(1)
         Set vDataOne = .Range("f1:f" & .Range("f65536").End(xlUp).Row)  ' error on this line
        End With


    The way you have it coded if you remove the .select it will work fine but you will be creating an array with the values from the cells, if that's what you want fine, but if you want a range use the code I posted.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!