Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If then on cells in a column

    Excel 97 SR2

    I have a worksheet with data in column A (starting with row 5), I want to be able to write a procedure that will check to see if the first cell (A5) is empty. If the cell is empty, end the procedure. If the cell has contents, then execute procedure #2. After executing procedure #2 on cell A5, repeat on the next row in column A. So on and so forth until there is no more data in column A.

    What code can I use to increment the row numbers to tell the procedure to move to the next cell in column A?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    You can use something similar to the code below

    <pre>Sub IncrementRow()
    Dim R As Range
    Dim cell As Range
    Set R = Range("A5:A100")
    For Each cell In R
    If Not IsEmpty(cell) Then
    MsgBox "The cell " & cell.Address & " is not empty"
    'call procedure#2
    Else
    MsgBox "The cell " & cell.Address & " is empty"
    'exit sub
    End If
    Next
    End Sub
    </pre>


  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    If I may be so bold, I would suggest a small improvement to Hans' code by modifying the following line:
    Set R = Range("A5:A100")
    to
    If range("a6")="" then
    Set R = Range("A5")
    else
    Set R = Range(Range("a5"),Range("a5").end(xldown))
    endif
    Then it doesn't matter how much data you have in the column, your code will always pick it up.

    Jon

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    Jon: I don't think that will work with empty cells in the column.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    You're right, Legare, it won't. It assumes that the data has no gaps in it.

    I (and apparently Hans) thought that the original question was asking for code that went through the data until it reached a blank row, though it does say "until there is no more data".

    Steve, if you have blank rows in your data, and you want some code that will work without hard coding the rows, 1 of any number of alternatives, which works with or without blank lines in the data, is:

    Dim R As Range
    Dim Cell As Range
    If Range("a5") <> "" Then
    Set R = Union(Columns(1).SpecialCells(xlCellTypeConstants) , Columns(1).SpecialCells(xlCellTypeFormulas))
    For Each Cell In R
    If Cell.Row > 5 Then Procedure2
    Next
    End If

    Jon

  6. #6
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    What I have is data starting in row 5, with the last possible row being 40. This same worksheet is used in many different workbooks. So each workbook contains a different amount of rows, but the way they are setup is that I start in row 5 and continue down until there is no more information.

    What i am trying to do is set formulas in columns B, C, D, and E in the corresponding row. The first procedure above works great for cycling through the rows, but I am confused how to refence the row number in procedure number 2.

    for example, the code should look at row 5 in column A to determine if the cell is empty or not. If empty, end the procedure. If not empty, then set formula1 in row 5 column B, set formula2 in row 5 column C, set formula3 in row 5 column D, and set formula 4 in row 5 column E. each formula will need to include the string data that is in the cell in row 5 column A.

    all help is greatly appreciated, I am learning alot.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    It is a little tough to talk about the forumla without knowing what it is, so this message will assume that you have created the formula for column B in a string variable named strFormula1, and that the formula is in A1 format not R1C1 format. You can use the following statement to put that formula in Column B of the same row as the variable R points to column A:

    <pre> R.Offset(0,1).Formula = strFormula1
    </pre>


    You just need to change the column offset for the other columns.
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    Here are the formulas that I am needing to put in. The phrase <font color=448800>**HELP**</font color=008080> is whatever is in the column A cell in that corresponding row. All of my worksheets are setup to reference cells using the A1 format.

    Formula1 = IF(ISNA(MATCH($E$3,<font color=448800>**HELP**</font color=008080>!$C:$C,0)),"prior to start",INDEX(<font color=448800>**HELP**</font color=008080>!$L:$L,MATCH($E$3,<font color=448800>**HELP**</font color=008080>!$C:$C,0)))

    Formula 2 = <font color=448800>**HELP**</font color=008080>!$K$6

    Formula 3 = IF(ISNA(MATCH($E$3,<font color=448800>**HELP**</font color=008080>!$C:$C,0)),"prior to start",INDEX(<font color=448800>**HELP**</font color=008080>!$N:$N,MATCH($E$3,<font color=448800>**HELP**</font color=008080>!$C:$C,0)))

    Formula 4 = IF(ISNA(MATCH($E$3,<font color=448800>**HELP**</font color=008080>!$C:$C,0)),"prior to start",INDEX(<font color=448800>**HELP**</font color=008080>!$P:$P,MATCH($E$3,<font color=448800>**HELP**</font color=008080>!$C:$C,0)))

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    As Legare said, you should build up your formulas as strings and then use his line of code.

    e.g.

    Formula1 = "IF(ISNA(MATCH...."
    R.offset(...).Formula = Formula1

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    See if these will give you what you want:

    <pre> Formula1 = "=IF(ISNA(MATCH($E$3," & R.Value & _
    "!$C:$C,0)),""prior to start"",INDEX(" & R.Value & _
    "!$L:$L,MATCH($E$3," & R.Value & "!$C:$C,0)))"

    Formula2 = "=" & R.Value & "!$K$6"

    Formula3 = "=IF(ISNA(MATCH($E$3," & R.Value & _
    "!$C:$C,0)),""prior to start"",INDEX(" & R.Value & _
    "!$N:$N,MATCH($E$3," & R.Value & "!$C:$C,0)))"

    Formula4 = "=IF(ISNA(MATCH($E$3," & R.Value & _
    "!$C:$C,0)),""prior to start"",INDEX(" & R.Value & _
    "!$P:$P,MATCH($E$3," & R.Value & "!$C:$C,0)))"
    </pre>

    Legare Coleman

  11. #11
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    Here is the code I am using, but when I run it I get a runtime error #13, type mismatch on the R.Offset line.

    <pre>Sub IncrementRow()
    Dim R As Range
    Dim cell As Range
    Set R = Range("a5:a40")
    For Each cell In R
    If Not IsEmpty(cell) Then
    MsgBox "the cell " & cell.Address & " is not empty"
    R.Offset(0, 1).Formula = "=IF(ISNA(MATCH($E$3," & R.Value & _
    "!$C:$C,0)),""prior to start"",INDEX(" & R.Value & _
    "!$L:$L,MATCH($E$3," & R.Value & "!$C:$C,0)))"
    Else
    MsgBox "the cell " & cell.Address & " is empty"
    Exit Sub
    End If
    Next

    End Sub
    </pre>


    <A target="_blank" HREF=http://stevehenderson.com/ExcelFiles.html> I have posted a copy of the excel file I am using on my website. </A>

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    Steve,

    You should replace R.Value by cell.value in the string.

  13. #13
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    I replace r.value with cell.value in the string, BUT, every row in the range was updated with the same formula. I was hoping that when the loop reads row 5 it modifies only row 5 then moves to row 6, without changing row 5 again.

    Ideas? I do appreciate your help.

  14. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    Sorry,

    You also have to change the line with R.offset to

    cell.Offset(0, 1).Formula = "..."

    Now it is filling the range of the same size as R immediately on the right of R.

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If then on cells in a column

    Sorry, my formula should have read:

    <pre> Formula1 = "=IF(ISNA(MATCH($E$3," & Cell.Value & _
    "!$C:$C,0)),""prior to start"",INDEX(" & Cell.Value & _
    "!$L:$L,MATCH($E$3," & Cell.Value & "!$C:$C,0)))"
    Formula2 = "=" & Cell.Value & "!$K$6"
    Formula3 = "=IF(ISNA(MATCH($E$3," & Cell.Value & _
    "!$C:$C,0)),""prior to start"",INDEX(" & Cell.Value & _
    "!$N:$N,MATCH($E$3," & Cell.Value & "!$C:$C,0)))"
    Formula4 = "=IF(ISNA(MATCH($E$3," & Cell.Value & _
    "!$C:$C,0)),""prior to start"",INDEX(" & Cell.Value & _
    "!$P:$P,MATCH($E$3," & Cell.Value & "!$C:$C,0)))"
    </pre>


    That replaces every R.Value with Cell.Value.

    That's what I get for trying to remenber what was in a different message than I am replying to without looking.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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