InStr Function

[fluxcapacitr609]

Dim LastRow As LongDim erow As Long
Dim i As Long
Dim strComp


LastRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
Sheet2.Select
Range("A1").Select


For i = 2 To LastRow


    
    strComp = Cells(i, 3)
    If InStr(1,strComp,"Fuel Consumed")= 1 Then
    erow = Sheet4.Cells(Rows.Count, 9).End(xlUp).Offset(1, 0).Row
    Range(Cells(i, 1), Cells(i, 4)).Copy Destination:=Sheet4.Range("I" & erow)
    End If

Im trying to search a column for a rows with the phrase "Fuel Consumed" in it then paste the rows with matching criteria in it into another sheet. I have a version of this that uses a date range and works.

Im getting an Sytax error at the "If" line.

 

[VBA Geek]

Im trying to search a column for a rows with the phrase "Fuel Consumed" in it then paste the rows with matching criteria in it into another sheet. I have a version of this that uses a date range and works.

Use Advanced Filter instead of looping:

http://www.contextures.com/exceladvancedfiltervba.html

 

[fluxcapacitr609]

I unfortunately cant do it that way. Im actually going to be combining it with other code to make it a nested loop with data being distributed throughout the workbook. Im just trying to get the syntax of the InStr Function correct.

 

[Fluff]

StrComp is a function, which is why you are getting the problem.
Change the name of your variable