Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: For, Next loop that checks each cell of one column

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •