Having an issue with VBA code that is trying to find a certain optional heading in row 1 (of a defined sheet in a separate file called DataSheet). I want to set an integer variable (JunkCol) to the column number of a specified heading ("Junk" in this example), or to a value of zero if the heading is not found. Code looks like this:
On Error Resume Next
JunkCol = 0
JunkCol = Worksheetfunction.Match("Junk",DataSheet.Rows(1),0)
Problem: This normally works fine, but if the macro happens to run an error-handling routine (for another issue), then thereafter I seem to be getting "Error 1004 - Unable to get the Match property..." whenever any match statement fails to find the desired heading (in spite of the On Error Resume Next). My error handler does clear the previous error, and I've even inserted code right before the match statement to ensure that any previous errors are cleared:
On Error Resume Next
JunkCol = 0
If Err <> 0 then Err.Clear
JunkCol = Worksheetfunction.Match("Junk",DataSheet.Rows(1),0)
Still no go; I still get the Error 1004 after the error handler runs. What's the problem? Why won't "On Error Resume Next" work, if any previous errors have been cleared?
Again, this works fine until my error handler runs, then I start getting Error 1004 on every match that fails. By the way, my error handler (when a REQUIRED heading isn't found) just pops up a message, clears the error, and jumps to the next data file, like this:
Response = Msgbox ("Required heading not found; this file will not be processed." ... )
Err.Clear
Goto NextDataFile