Help with macros

[MARKEGANDERSON]

HELLO ALL:

i am trying to redo a Macro. I can figure out what most of the formulas are.

Can anyone tells me what "i" signifies in the below?
If Cells(i, 5).


Sub HQ()
Dim lusedrow As Long
Dim desc As String
Dim wkb As Workbook

Set wkb = ActiveWorkbook

wkb.Worksheets("Theater Data").Activate

lusedrow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
MsgBox lusedrow

For i = 2 To lusedrow
'Check for SUV
If Cells(i, 5).Value Like "*SUV*" And Cells(i, 7).Value < 556 Then
Cells(i, 8).Value = "U"

 

[MrKowz]

i is a counter that is being looped through.

In the Cells(i, 5), we first need to look at the syntax of the Cells() function.

Cells(x,y) represents a range object of row x and column y.

So to refer to the cell G3, we could write:

Range("G3")
or
Cells(3,7)
or
Cells(3,"G")

So in your above code, Cells(i, 5) represents the row that the counter i is currently on, in column E.

 

[lenze]

i is the variable in your loop which signifies the row number. Your macro starts with i =2 and loops, (increasing i by 1) until the lastrow is reached. So when i = 5,
Cells(i,5) refers to Cells (5,5) or $E$5!!
lenze

 

[MARKEGANDERSON]

Thanks guys-

So if i wanted it to search column g i would write (i,7)?

 

[MrKowz]

Thanks guys-

So if i wanted it to search column g i would write (i,7)?

Correct

 

[MARKEGANDERSON]

how about this?

What does the "7" signifies?

lusedrow = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
MsgBox lusedrow

 

[Dryver14]

yes to search column G you would use (i,7) if it is of a different lengh to column 5 though, for instance shorter it would stop at the lengh of column 5 at the moment as this line in the code

lusedrow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row

is looking for the last row in column 5 or "E" as well, you may need to change that reference to (Rows.count, 7)

 

[lenze]

See Post #2 by MrKowz

lenze

 

[MARKEGANDERSON]

I keep getting an error message:compile error: next without for

can you please help?

Sub HQ()
Dim lusedrow As Long
Dim desc As String
Dim wkb As Workbook

Set wkb = ActiveWorkbook

wkb.Worksheets("ATV").Activate

lusedrow = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
MsgBox lusedrow

For i = 2 To lusedrow
'Light Wheel
If Cells(i, 7).Value Like "*Light Wheel*" And Cells(i, 9).Value < 556 Then
Cells(i, 10).Value = "U"
ElseIf Cells(i, 7).Value Like "*Light Wheel*" And Cells(i, 9).Value < 889 And Cells(i, 9).Value >= 556 Then
Cells(i, 10).Value = "M"
ElseIf Cells(i, 7).Value Like "*Light Wheel*" And Cells(i, 9).Value >= 889 Then
Cells(i, 10).Value = "O"

'Bus
If Cells(i, 7).Value Like "*Passenger Bus*" And Cells(i, 9).Value < 667 Then
Cells(i, 10).Value = "U"
ElseIf Cells(i, 7).Value Like "*Passenger Bus*" And Cells(i, 9).Value < 1067 And Cells(i, 9).Value >= 667 Then
Cells(i, 10).Value = "M"
ElseIf Cells(i, 7).Value Like "*Passenger Bus*" And Cells(i, 9).Value >= 1067 Then
Cells(i, 10).Value = "O"

End If

Next i
End Sub

 

[lenze]

I think you are missing an End if

Cells(i, 10).Value = "O"
End If
'Bus