fixing run time error 1004 with WorksheetFunction.VLookup

[abdelfattah]

hi
i need help to fixing the error in this line

 If Not IsError(Application.WorksheetFunction.VLookup(.Range("A" & i).Value, SearchRange, 2, False)) Then

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

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

 

[Domenic]

Use the VLookup method of the Application object, instead of the WorksheetFunction object...

If Not IsError(Application.VLookup(.Range("A" & i).Value, SearchRange, 2, False)) Then

As such, when there's no match, it causes a non-breaking error.

Hope this helps!

 

[abdelfattah]

hi, domenic it works thanks but i have one thing is there way when the run macro only one time not repeatedly

 

[Domenic]

Do you mean that once the lookup value is found that you want to exit the For/Next loop? If not, please clarify?

 

[abdelfattah]

actually i note my code needs some improve when i run macro more than time it continues subtract and change the values permanently this causes the problem maybe i make mistake and run repeatedly so if there is way to show message " you repeat this process ,are you sure that" if i press ok then continue subtracting if press no then nothing happens

 

[Domenic]

Then how about designating a cell to keep track of the status? So, for example, when the macro runs and is completed, it enters "Completed" or "Updated" in the designated cell. Then, when the macro is run again, it checks the cell to see whether it has already been updated, and proceed accordingly.

Would this work for you?

 

[abdelfattah]

it checks the cell to see whether it has already been updated, and proceed accordingly.

this is what i 'm looking for

 

[Domenic]

I have amended your macro so that Sheets("Sheet2").Range("a1") will keep track of the update status. You can change this cell as desired. Now, when the macro is run, it first checks the update status, and then proceeds accordingly.

You'll notice that I have removed On Error Resume Next and On Error GoTo 0 since Application.VLookup causes a non-breaking error and, therefore, is not needed.

Option Explicit

Sub Sample()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ws1LastRow As Long, ws2LastRow As Long, i As Long
    Dim StatusRange As Range, SearchRange As Range
    Dim RetVal As Variant
  
    Set ws2 = Sheets("Sheet2")
  
    Set StatusRange = ws2.Range("A1") 'to keep track of the update status (change the designated cell as desired)

    If StatusRange.Value = "Updated" Then
        If MsgBox("Already updated, continue?", vbQuestion + vbYesNo) = vbNo Then
            Exit Sub
        End If
    End If

    Set ws1 = Sheets("Sheet1")
    ws1LastRow = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row

    Set SearchRange = ws1.Range("A1:B" & ws1LastRow)

    With ws2
        ws2LastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 2 To ws2LastRow
            RetVal = Application.VLookup(.Range("A" & i).Value, SearchRange, 2, False)
            If Not IsError(RetVal) Then
                .Range("B" & i).Value = .Range("B" & i).Value - RetVal
            End If
        Next i
    End With
  
    StatusRange.Value = "Updated"

    '~~> Clear Sheet1 for next input
    ws1.Cells.ClearContents

    '~~> Clean Up
    Set SearchRange = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

Hope this helps!

 

[abdelfattah]

brilliant ! great work this what i want many thanks

 

[Domenic]

You're very welcome, glad I could help, cheers!