Help with macros

MARKEGANDERSON

Active Member
Joined
Apr 7, 2007
Messages
256
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"
 

Some videos you may like

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
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Feb 18, 2002
Messages
13,690
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
Joined
Apr 7, 2007
Messages
256
how about this?

What does the "7" signifies?

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

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396

ADVERTISEMENT

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)
 

MARKEGANDERSON

Active Member
Joined
Apr 7, 2007
Messages
256
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

Legend
Joined
Feb 18, 2002
Messages
13,690
I think you are missing an End if
Rich (BB code):
Cells(i, 10).Value = "O"
End If
'Bus
 

Watch MrExcel Video

Forum statistics

Threads
1,108,626
Messages
5,523,981
Members
409,550
Latest member
baaabies

This Week's Hot Topics

Top