For, Next loop that checks each cell of one column

jrnyman · Mar 11th, 2002, 07:01 AM

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 · Mar 11th, 2002, 07:38 AM

One way to do it is this:



  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

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":



    If JDataRow.Value = "Average" Then

      JDataRow.Font.Bold = True

    End If

HTH

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-03-11 06:39 ]

Dave Hawley · Mar 11th, 2002, 07:50 AM

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 ]

Mar 11th, 2002, 07:38 AM	#2
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	One way to do it is this: 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 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": If JDataRow.Value = "Average" Then JDataRow.Font.Bold = True End If HTH _________________ [b] Mark O'Brien [ This Message was edited by: Mark O'Brien on 2002-03-11 06:39 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 07:01 AM	#1
jrnyman Board Regular Join Date: Mar 2002 Location: Boston, MA Posts: 105	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

Mar 11th, 2002, 07:50 AM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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 ]