For, Next loop that checks each cell of one column

[jrnyman]

I need to set up a For, Next routine that checks each cell in Column J to see if it equals "Average". If it does, I have a series of If commands, otherwise, it needs to move to the next row. I anticipated doing this by defining a counting variable to be the row number and just using J as the Column, but i am not sure how to reference a cell where the Column is known, but the row is variable. Here's my rough sketch of the idea:

For DataRow = 2 to LastRow [LastRow is calculated earlier]
If JDataRow = "Average"
......
Next DataRow

 

[Mark O'Brien]

One way to do it is this:<pre>
For DataRow = 2 To LastRow '[LastRow is calculated earlier]
Set JDataRow = Sheets("Sheet1").Range("J" & DataRow)
If JDataRow.Value = "Average" Then
MsgBox "Hi"
End If
Next DataRow</pre>

I like this way because then JDataRow is actually directly references the range.
You can then easily set stuff like formatting and cell contents, for example to change the cell text to bold if the cell value is "Average":<pre>
If JDataRow.Value = "Average" Then
JDataRow.Font.Bold = True
End If</pre>

HTH


_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-03-11 06:39

 

[Dave Hawley]

Hi rnyman

You maybe able to avoid some If statements if you need only True or False.

Dim rDataRow As Range
For Each rDataRow In Range("J2", Range("J65536").End(xlUp))
rDataRow.Font.Bold = (rDataRow = "Average")
Next rDataRow

If you do have multiple If statements you might consider the Select Case.


_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training
If it's Excel, then it's us!
This message was edited by Dave Hawley on 2002-03-11 06:51