assign a cell value to a variable vba loop

[lostinvb]

I would like the correct way to assign cells to a variable in a loop like below?



For i = 1 To Rows.Count


ItemNumber = cell(i,1) Description = cell(i, 2)
ID = cell( i. 3)
Price = cell(i,8)
Quantity = cell(i,9)

' will be printing a label with the above on a label

Next I

[Paul_Hossler]

Top of my head






For i = 1 To Rows.Count
ItemNumber = Cellsl(i,1).Value
Description = Cells(i, 2).Value
ID = Cells(i, 3).Value
Price = Cells(i,8).Value
Quantity = Cells(i,9).Value


' will be printing a label with the above on a label


Next i

[lostinvb]

Thanks Paul, I was making it harder than I thought. Was thinking I would have to do offset or active cell or something.
With reguards to the above and below code I am getting a row count of 65000+ instead of the 10 in my sample file?
There is no blanks in column A, would there be a better way?


Private Sub UserForm_Initialize()


Dim d As Variant
Dim O As Integer
Dim ID As String
Dim Description As String
Dim Price As Currency
Dim ItemNumber As String
Dim I As Long


O = 298

For I = 1 To Rows.Count
SMC2 = O + (Month(Now))
lblPrintSMC.Caption = SMC2

ItemNumber = Cells(I, 1).Value
Description = Cells(I, 2).Value
ID = Cells(I, 3).Value
Price = Cells(I, 8).Value
Quantity = Cells(I, 9).Value

lblPrintCost.Caption = ID
lblPrintDescription.Caption = Left(Description, 34)
lblPrintPrice.Caption = Price
lblPrintItemNumber = ItemNumber
d = Date
lblDate = d

frmPopup2.PrintForm
Next I


End Sub

[Paul_Hossler]

With reguards to the above and below code I am getting a row count of 65000+ instead of the 10 in my sample file?


'Rows' used like that refers to the entire sheet

Using a 'dot' inside a With/End With refers to the parent

So .Cells(I,8) refers to the object in the With, i.e. Activesheet , or Activesheet.Cells(I,8)


This is not tested



Option Explicit


Private Sub UserForm_Initialize()
Dim d As Variant
Dim O As Integer
Dim ID As String
Dim Description As String
Dim Price As Currency
Dim ItemNumber As String
Dim I As Long
Dim r1 As Range, r2 As Range
Dim SMC2 As Long ' probably a Long :-)
With ActiveSheet

Set r1 = .Cells(1, 1) ' A1
Set r2 = r1.End(xlDown) ' last cell with data down from A1, say A10

O = 298

For I = 1 To Range(r1, r2).Rows.Count ' 1 to A1:A10 row, i.e. 10
SMC2 = O + (Month(Now))
lblPrintSMC.Caption = SMC2

ItemNumber = .Cells(I, 1).Value
Description = .Cells(I, 2).Value
ID = .Cells(I, 3).Value
Price = .Cells(I, 8).Value
Quantity = .Cells(I, 9).Value

lblPrintCost.Caption = ID
lblPrintDescription.Caption = Left(Description, 34)
lblPrintPrice.Caption = Price
lblPrintItemNumber = ItemNumber
d = Date
lblDate = d

frmPopup2.PrintForm
Next I
End With

End Sub