If then on cells in a column

Topic

New Reply

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?

Reply | Quote

Replies

You can use something similar to the code below

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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:

    R.Offset(0,1).Formula = strFormula1

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

Reply | Quote

Here are the formulas that I am needing to put in. The phrase **HELP** 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,**HELP**!$C:$C,0)),”prior to start”,INDEX(**HELP**!$L:$L,MATCH($E$3,**HELP**!$C:$C,0)))

Formula 2 = **HELP**!$K$6

Formula 3 = IF(ISNA(MATCH($E$3,**HELP**!$C:$C,0)),”prior to start”,INDEX(**HELP**!$N:$N,MATCH($E$3,**HELP**!$C:$C,0)))

Formula 4 = IF(ISNA(MATCH($E$3,**HELP**!$C:$C,0)),”prior to start”,INDEX(**HELP**!$P:$P,MATCH($E$3,**HELP**!$C:$C,0)))

Reply | Quote

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

Reply | Quote

See if these will give you what you want:

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

Reply | Quote

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.

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

I have posted a copy of the excel file I am using on my website.

Reply | Quote

Steve,

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Sorry, my formula should have read:

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

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.

Reply | Quote

Thank you both, Hans and Legare, with the change to cell.value, everything works perfectly.

You have saved me many hours of work.

Reply | Quote

I guess I spoke to soon.

Everything worked great on my PC at home, but now that I am at work I cannot get the code to work. I am getting a run-time error ‘1004’: Application-defined or object-defined error.

I believe this means I don’t have the correct libraries referenced, but I don’t know which one(s) to add.

Here are the ones that I have selected on my machine at work:
–Visual Basic for Applications
–Microsoft Excel 8.0 Object Library
–OLE Automation
–Microsoft Forms 2.0 Object Library
–Microsoft Office 8.0 Object Library

What am I missing?? I am still running Excel97 SR2.

Reply | Quote

I haven’t seen anything in the code that has been posted so far that should need a reference. Can you show us the code and tell us which statement Excel is having the problem with?

Reply | Quote

Boy, do I feel stupid!

the cell that my formula references is a one-word string, but the worksheet I ran the code in today contained a two-word string separated by a comma. That is what the entire problem was.

I should never attempt work on a Monday morning.

Reply | Quote

That sounds like something I do at least once a day.

Reply | Quote