# Help with macros

#### MARKEGANDERSON

##### Active Member
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"

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### MrKowz

##### Well-known Member
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

##### Legend
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

##### Active Member
Thanks guys-

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

#### MrKowz

##### Well-known Member

Thanks guys-

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

Correct

#### MARKEGANDERSON

##### Active Member

What does the "7" signifies?

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

#### Dryver14

##### Well-known Member

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

##### Legend
See Post #2 by MrKowz

lenze

#### MARKEGANDERSON

##### Active Member
I keep getting an error message:compile error: next without for

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

##### Legend
I think you are missing an End if
Rich (BB code):
``````Cells(i, 10).Value = "O"
End If
'Bus``````

Replies
4
Views
71
Replies
7
Views
97
Replies
1
Views
124
Replies
3
Views
148
Replies
6
Views
55