1004 error. unable to get the Match property of the WorksheetFunction class.

[eugenewong]

Hi

I'm currently trying to obtain data from the user from first sheet1 and then do the following tasks:
1) vlookup some values from sheet2 (which is successful i guess)
2) find the string given in sheet1 in sheet2 and change the value in the next column in sheet2 (i'm sorry for the lousy description but i hope u get the idea)

i can't seem to get the 2nd part working. Can somebody help me? it gives me the error "1004, unable to get the Match property of the WorksheetFunction class"

Sub findReplace()
    
    Dim i As Long, j As Long, LR As Long
    Dim dataRow As Long
    
     LR = Cells(Rows.Count, 1).End(xlUp).row
     
        
        For j = 2 To LR
            Sheets("sheet1").Cells(j, 2) = Application.VLookup(Sheets("sheet1").Cells(j, 1), Sheets("Sheet2").Columns("b:e"), 2, False)
            Sheets("sheet1").Cells(j, 3) = Application.VLookup(Sheets("sheet1").Cells(j, 1), Sheets("Sheet2").Columns("b:e"), 3, False)
        
        Next j
      
        
        For i = 2 To LR
        
            dataRow = Application.WorksheetFunction.Match(Sheets("sheet1").Cells(i, 1), Sheets("sheet2").Range("b1:b100"), 0)
    
            Sheets("sheet2").Cells(dataRow, 3) = Cells(dataRow, 3) - 1
    
        Next i
    
End Sub

Any help is greatly appreciated and do correct me if i did anything wrong as i'm still new to VB. thanks!

 

[villy]

Hi

I'm currently trying to obtain data from the user from first sheet1 and then do the following tasks:
1) vlookup some values from sheet2 (which is successful i guess)
2) find the string given in sheet1 in sheet2 and change the value in the next column in sheet2 (i'm sorry for the lousy description but i hope u get the idea)

i can't seem to get the 2nd part working. Can somebody help me? it gives me the error "1004, unable to get the Match property of the WorksheetFunction class"

Sub findReplace()
 
    Dim i As Long, j As Long, LR As Long
    Dim dataRow As Long
 
     LR = Cells(Rows.Count, 1).End(xlUp).row
 
 
        For j = 2 To LR
            Sheets("sheet1").Cells(j, 2) = Application.VLookup(Sheets("sheet1").Cells(j, 1), Sheets("Sheet2").Columns("b:e"), 2, False)
            Sheets("sheet1").Cells(j, 3) = Application.VLookup(Sheets("sheet1").Cells(j, 1), Sheets("Sheet2").Columns("b:e"), 3, False)
 
        Next j
 
 
        For i = 2 To LR
           [COLOR=red]With WorksheetFunction.Application[/COLOR]
            dataRow = .Match(Sheets("sheet1").Cells(i, 1), Sheets("sheet2").Range("b1:b100"), 0)
 
            Sheets("sheet2").Cells(dataRow, 3) = Cells(dataRow, 3) - 1
            [COLOR=red]End With[/COLOR]
        Next i
 
End Sub

Any help is greatly appreciated and do correct me if i did anything wrong as i'm still new to VB. thanks!

Try something like this (in red) I usually do it when I get the same Error...

 

[eugenewong]

I still get the same error. Is there something wrong with my LR?
it was intended to loop until the last row with a string entered by the user.