abdelfattah
Well-known Member
- Joined
- May 3, 2019
- Messages
- 1,429
- Office Version
- 2019
- 2010
- Platform
- Windows
hi
i need help to fixing the error in this line
it shows me this error run time error 1004 with WorksheetFunction.VLookup the code supposes updating sheet2 by subtracting the values in col b from sheet1
i need help to fixing the error in this line
VBA Code:
If Not IsError(Application.WorksheetFunction.VLookup(.Range("A" & i).Value, SearchRange, 2, False)) Then
Code:
Sub Sample()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim ws1LastRow As Long, ws2LastRow As Long, i As Long
Dim SearchRange As Range
Set ws1 = Sheets("Sheet1")
ws1LastRow = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
Set SearchRange = ws1.Range("A1:B" & ws1LastRow)
Set ws2 = Sheets("Sheet2")
With ws2
ws2LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To ws2LastRow
On Error Resume Next
If Not IsError(Application.WorksheetFunction.VLookup(.Range("A" & i).Value, SearchRange, 2, False)) Then
.Range("B" & i).Value = .Range("B" & i).Value - _
Application.WorksheetFunction.VLookup(.Range("A" & i).Value, SearchRange, 2, False)
End If
On Error GoTo 0
Next i
End With
'~~> Clear Sheet1 for next input
ws1.Cells.ClearContents
'~~> Clean Up
Set SearchRange = Nothing
Set ws1 = Nothing
Set ws2 = Nothing
End Sub