fixing run time error 1004 with WorksheetFunction.VLookup

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010
hi
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
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
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Use the VLookup method of the Application object, instead of the WorksheetFunction object...

VBA Code:
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

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010
hi, domenic it works thanks but i have one thing is there way when the run macro only one time not repeatedly
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Do you mean that once the lookup value is found that you want to exit the For/Next loop? If not, please clarify?
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010

ADVERTISEMENT

it checks the cell to see whether it has already been updated, and proceed accordingly.
this is what i 'm looking for
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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.

VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,483
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top