Ranges and Cells

[RicksAtWork]

I need to iterate through the rows in a range



For Each lookUpRow In UKStaffRange.Rows



Next lookUpRow


I want to get the values from the 1st and 2nd cells

How do I do this?

 

[RicksAtWork]

Missed out an important point...

I want to get the values from the 1st and 2nd cells for EACH row

How do I do this?

 

[mrmovie]

'should get this from something like ActiveCell.CurrentRegion etc
intNumRows = 25

For i = 1 To intNumRows
Msgbox CStr(Cells(i, 1).Value) & " " CStr(Cells(i, 2).Value)
Next

 

[RicksAtWork]

will lookUpRow not have a cells collection in this case?

 

[Chance1234]

Sub test()

Dim r


For Each r In Range("UKStaffRange").Rows
 
  
    Debug.Print Cells(r.Row, 1).Value
    Debug.Print Cells(r.Row, 2).Value
    
Next r
    
    
End Sub

Chance,

Filmmaker, taken gentleman and countdown to lucrative licence deal

 

[Chance1234]

footnote,

re columns the above was having my range starting in A1

you could also use the following to be more specific about columns,

Sub test()

Dim r


For Each r In Range("UKStaffRange").Rows
 
    For Each c In r.Columns
        Select Case c.Column
        Case 2
            Debug.Print Cells(r.Row, c.Column).Value
        Case 3
            Debug.Print Cells(r.Row, c.Column).Value
        End Select
    Next

Next r
    
    
End Sub

Also take a look at the address and offset properties


Chance,

Filmmaker, taken gentleman and countdown to lucrative licence deal

 

[RicksAtWork]

Cheers for your help.

If I'm iterating through cells i.e.


For Each targetCell In targetStaffRange.Cells

Next targetCell

can I get the row number of the current cell?

targetStaffRange is just a range 1 column wide

It comes from a sheet that contains many columns.

Is it possible to set the value of a cell ie.e

Sheets("sheet1").Rows(1,1).Value= 4

??

 

[Chance1234]

yeap,

For Each targetCell In targetStaffRange.Cells
debug.print r.row
Next targetCell

not sure what you are asking in the second part



Chance,

Filmmaker, taken gentleman and countdown to lucrative licence deal

 

[mrmovie]

if you are iterating through cell objects then you can

targetCell.Value = "vuy

 

[RicksAtWork]

Aye.. my final code reads:

For Each targetCell In targetStaffRange.Cells

'Get the individuals name for this row
targetIndividual = targetCell.Value
targetRow = targetCell.row

For Each lookUpRow In UKStaffRange.Rows

staff = (lookUpRow.Cells(1, 1))

If staff = targetIndividual Then

safeID = (lookUpRow.Cells(1, 2))

Sheets(targetWorkSheetName).Cells(targetRow, 7).Value = safeID


End If

Next lookUpRow


Next targetCell


Thanks for all your help everyone!