Vlookup error in VBA, 2042
 

I'm trying to do a simple vlookup in Excel but getting an error when I step
through the process...
Anyone know what's wrong?

Public Sub SyncSheet2()
Const ArtCol As String = "A" '<=== change to suit
Dim i As Long
Dim T As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim NextRow As Long
Dim CellVal As Variant
Dim DataCol As Variant
Dim ArtNum As String
Dim LookUpRng As Range
Dim sh As Worksheet

Set sh = Sheet2


With sh

i = 1
Do Until FirstRow < 0
If IsNumeric(.Cells(i, "A")) Then
If .Cells(i, "A").Value 0 Then
FirstRow = i
End If
Else: FirstRow = 0
End If
i = i + 1
Loop
i = 1
Do Until LastRow < 0
If .Cells(i + 2, "A").Value = "" Then
LastRow = i + 1
Else: LastRow = 0
i = i + 1
End If
Loop
End With

With sh
T = 2

For i = FirstRow To LastRow
ArtNum = sh.Cells(i, ArtCol)
CellVal = Application.VLookup(ArtNum, Sheet1.Range("A1:V306"), T,
False)
If IsError(CellVal) Then
CellVal = "Error"
End If
With sh.Cells(i, "B")
.Value = CellVal
End With

Vlookup error in VBA, 2042
 

When using vlookup, always precede it with the 'On Error Resume Next'
statement, because if the vlookup does not find a match an error will occure.
In your case because the youre are looping through a range to lookup a
'ArtNum' the 'On Error Resume Next' statement will cause the code to carry on
to the next cell if it did not find it yet without causing a run-time error.
If it does find a match to 'ArtNum' the value of that cell will be asigned to
sh.Cells(i, "B").Value = CellVal. If it finds it more then once it will asign
the last occurence to sh.Cells(i, "B").Value = CellVal which is the same
value as before nbeing that youre lookink for the same value. You should
really add the following code which will prevent unnecesary code from running:

If Err.Description = 0 Then ' Found match then exit for
Exit For
End If

Hope this helps!
--
A. Ch. Eirinberg


"elf27" wrote:

I'm trying to do a simple vlookup in Excel but getting an error when I step
through the process...
Anyone know what's wrong?

Public Sub SyncSheet2()
Const ArtCol As String = "A" '<=== change to suit
Dim i As Long
Dim T As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim NextRow As Long
Dim CellVal As Variant
Dim DataCol As Variant
Dim ArtNum As String
Dim LookUpRng As Range
Dim sh As Worksheet

Set sh = Sheet2


With sh

i = 1
Do Until FirstRow < 0
If IsNumeric(.Cells(i, "A")) Then
If .Cells(i, "A").Value 0 Then
FirstRow = i
End If
Else: FirstRow = 0
End If
i = i + 1
Loop
i = 1
Do Until LastRow < 0
If .Cells(i + 2, "A").Value = "" Then
LastRow = i + 1
Else: LastRow = 0
i = i + 1
End If
Loop
End With

With sh
T = 2

For i = FirstRow To LastRow
ArtNum = sh.Cells(i, ArtCol)
CellVal = Application.VLookup(ArtNum, Sheet1.Range("A1:V306"), T,
False)
If IsError(CellVal) Then
CellVal = "Error"
End If
With sh.Cells(i, "B")
.Value = CellVal
End With

Vlookup error in VBA, 2042
 

Howard,
Thanks. It wasn't clear from my code but I am using the For i... because I
want to do this for a number of values in the ArtNum column. Therefore, I
don't think your exit for code applies.
However, I think you are right on the on error resume next bit. Where should
I put that?
Any other ideas on why it's not working? I know the data's in there...



"Howard31" wrote:

When using vlookup, always precede it with the 'On Error Resume Next'
statement, because if the vlookup does not find a match an error will occure.
In your case because the youre are looping through a range to lookup a
'ArtNum' the 'On Error Resume Next' statement will cause the code to carry on
to the next cell if it did not find it yet without causing a run-time error.
If it does find a match to 'ArtNum' the value of that cell will be asigned to
sh.Cells(i, "B").Value = CellVal. If it finds it more then once it will asign
the last occurence to sh.Cells(i, "B").Value = CellVal which is the same
value as before nbeing that youre lookink for the same value. You should
really add the following code which will prevent unnecesary code from running:

If Err.Description = 0 Then ' Found match then exit for
Exit For
End If

Hope this helps!
--
A. Ch. Eirinberg


"elf27" wrote:

I'm trying to do a simple vlookup in Excel but getting an error when I step
through the process...
Anyone know what's wrong?

Public Sub SyncSheet2()
Const ArtCol As String = "A" '<=== change to suit
Dim i As Long
Dim T As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim NextRow As Long
Dim CellVal As Variant
Dim DataCol As Variant
Dim ArtNum As String
Dim LookUpRng As Range
Dim sh As Worksheet

Set sh = Sheet2


With sh

i = 1
Do Until FirstRow < 0
If IsNumeric(.Cells(i, "A")) Then
If .Cells(i, "A").Value 0 Then
FirstRow = i
End If
Else: FirstRow = 0
End If
i = i + 1
Loop
i = 1
Do Until LastRow < 0
If .Cells(i + 2, "A").Value = "" Then
LastRow = i + 1
Else: LastRow = 0
i = i + 1
End If
Loop
End With

With sh
T = 2

For i = FirstRow To LastRow
ArtNum = sh.Cells(i, ArtCol)
CellVal = Application.VLookup(ArtNum, Sheet1.Range("A1:V306"), T,
False)
If IsError(CellVal) Then
CellVal = "Error"
End If
With sh.Cells(i, "B")
.Value = CellVal
End With

Vlookup error in VBA, 2042
 

Put the 'On Error Resume Next' as follows:

On Error Resume Next
CellVal = Application.VLookup(ArtNum, Sheet1.Range("A1:V306"), T, False)

--
A. Ch. Eirinberg


"elf27" wrote:

Howard,
Thanks. It wasn't clear from my code but I am using the For i... because I
want to do this for a number of values in the ArtNum column. Therefore, I
don't think your exit for code applies.
However, I think you are right on the on error resume next bit. Where should
I put that?
Any other ideas on why it's not working? I know the data's in there...



"Howard31" wrote:

When using vlookup, always precede it with the 'On Error Resume Next'
statement, because if the vlookup does not find a match an error will occure.
In your case because the youre are looping through a range to lookup a
'ArtNum' the 'On Error Resume Next' statement will cause the code to carry on
to the next cell if it did not find it yet without causing a run-time error.
If it does find a match to 'ArtNum' the value of that cell will be asigned to
sh.Cells(i, "B").Value = CellVal. If it finds it more then once it will asign
the last occurence to sh.Cells(i, "B").Value = CellVal which is the same
value as before nbeing that youre lookink for the same value. You should
really add the following code which will prevent unnecesary code from running:

If Err.Description = 0 Then ' Found match then exit for
Exit For
End If

Hope this helps!
--
A. Ch. Eirinberg


"elf27" wrote:

I'm trying to do a simple vlookup in Excel but getting an error when I step
through the process...
Anyone know what's wrong?

Public Sub SyncSheet2()
Const ArtCol As String = "A" '<=== change to suit
Dim i As Long
Dim T As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim NextRow As Long
Dim CellVal As Variant
Dim DataCol As Variant
Dim ArtNum As String
Dim LookUpRng As Range
Dim sh As Worksheet

Set sh = Sheet2


With sh

i = 1
Do Until FirstRow < 0
If IsNumeric(.Cells(i, "A")) Then
If .Cells(i, "A").Value 0 Then
FirstRow = i
End If
Else: FirstRow = 0
End If
i = i + 1
Loop
i = 1
Do Until LastRow < 0
If .Cells(i + 2, "A").Value = "" Then
LastRow = i + 1
Else: LastRow = 0
i = i + 1
End If
Loop
End With

With sh
T = 2

For i = FirstRow To LastRow
ArtNum = sh.Cells(i, ArtCol)
CellVal = Application.VLookup(ArtNum, Sheet1.Range("A1:V306"), T,
False)
If IsError(CellVal) Then
CellVal = "Error"
End If
With sh.Cells(i, "B")
.Value = CellVal
End With

Vlookup error in VBA, 2042
 

You shouldn't have to use "on error resume next" when you use
application.vlookup() in your code. You would need it for
application.worksheetfunction.vlookup(), though.

Where is your code located?

When it was in a general module, it worked fine for me.

What do you have checked (in the VBE):
Tools|Options|General tab|error trapping section
I have "break in class module" checked.




elf27 wrote:

I'm trying to do a simple vlookup in Excel but getting an error when I step
through the process...
Anyone know what's wrong?

Public Sub SyncSheet2()
Const ArtCol As String = "A" '<=== change to suit
Dim i As Long
Dim T As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim NextRow As Long
Dim CellVal As Variant
Dim DataCol As Variant
Dim ArtNum As String
Dim LookUpRng As Range
Dim sh As Worksheet

Set sh = Sheet2

With sh

i = 1
Do Until FirstRow < 0
If IsNumeric(.Cells(i, "A")) Then
If .Cells(i, "A").Value 0 Then
FirstRow = i
End If
Else: FirstRow = 0
End If
i = i + 1
Loop
i = 1
Do Until LastRow < 0
If .Cells(i + 2, "A").Value = "" Then
LastRow = i + 1
Else: LastRow = 0
i = i + 1
End If
Loop
End With

With sh
T = 2

For i = FirstRow To LastRow
ArtNum = sh.Cells(i, ArtCol)
CellVal = Application.VLookup(ArtNum, Sheet1.Range("A1:V306"), T,
False)
If IsError(CellVal) Then
CellVal = "Error"
End If
With sh.Cells(i, "B")
.Value = CellVal
End With

--

Dave Peterson