VLOOKUP VBA code results in #NAME?

[Davefrisch]

At times I will run a macro with a vlookup function and it will return #NAME? in the first destination cell stopping the whole process. Other times it will return #REF. Most of the time it works very well, and I have checked to make sure the array is labeled appropriately each time this occurs.

The interesting part of this whole process is that when I copy and paste the whole excel page into notepad, then open up a new excel and paste the information from notepad into the new excel worksheet the macro code will work just fine. Any insight?

 

[Richard Schollar]

Hi

Difficult to come to any conclusions without seeing your code first

 

[Davefrisch]

The main section of the code that stops the process is listed below.

With theSheet.Cells(curRow + 1, (col + 1))
.NumberFormat = "General"
.FormulaR1C1 = "=VLOOKUP(RC[-1],myTab,2,FALSE)"
End With



With theSheet.Cells(curRow - 1, 1)
.Value = "JOB TITLE " & theSheet.Cells(curRow + 1, col).Value & _
" - " & theSheet.Cells(curRow + 1, col + 1).Value
.Font.Size = 18
.Rows.AutoFit
End With


After I select debug, it will highlight the following out of the above code:

.Value = "JOB TITLE " & theSheet.Cells(curRow + 1, col).Value & _
" - " & theSheet.Cells(curRow + 1, col + 1).Value

 

[Davefrisch]

Any ideas why moving it to notepad would solve this?

 

[Norie]

Dave

What is myTab?

A named range or a variable in the code?

 

[Davefrisch]

Oh yes, sorry. It is the name of the range for which the vlookup references.