Error 1004 in VBA program

Question

Hi,

I am writing a VBA Macro for work (I am new to this but took an on-line tutorial).  I am getting an Error 1004 ("Application-defined or object-defined error") when I run it.

The program up until the Error occurs is:

https://fud.community.services.support.microsoft.com/Fud/FileDownloadHandler.ashx?fid=c920c487-c6dc-46b7-89af-54a7941a8405

Using the Debug function, I have determined that the Error occurs at the line highlighted in yellow.

The file for this macro has two worksheets, "Working - Open NCASTS" and "AssyData".  For each row in "Working - Open NCASTS", the macro takes the string value in Column A and looks for a cell in "AssyData" with the same string value.  In the row in "AssyData" containing the found value (which are all in Column B), the macro gets the string value in the 26th column from Column B (i.e. Column AA) and assigns this string value to the variable "RCAcomments".

I have done the following troubleshooting steps:

• tried the VLOOKUP function in the workbook (i.e. not using VBA) using the same range  - it worked;
• changing the "26" number in the offending line to something less - no effect;
• verified using a Message Box that the NCASTS string value is correct;  and
• changed the "B4:AA261" range to something much less - no effect.

Any help would be appreciated.  Many thanks.

[Moved from: Office / Excel / Windows 8 / Unknown/other]

Answer 1

Just before your error line add the following

RCAcomments = "Not Found"

On Error Resume Next

Answer 2

It works!  Thanks for all your help and have a good weekend!

Answer 3

Hi RonaldoOneNil,

Thanks a lot for your info.  I didn't know that the Error occurs if the string cannot be found in the VLOOKUP range.  I actually knew in advance that there would be instances where the string would not be found (including the very first one, which is probably why I was getting the Error right away).

I was hoping that the line would return the value "#N/A", as the VLOOKUP function does when used within the spreadsheet (i.e. outside of VBA), rather than returning an Error.

I will have to think of a way around that.  Do you have any ideas?

Thanks!

Answer 4

That error occurs when the NCASTS string cannot be found in the VLOOKUP range. Are you sure the values are exactly the same ? Could one be a string and the other a number or a spurious space on the end of the string that you haven't noticed ?