Corresponding Row Question.

  • Thread starter Thread starter Dow
  • Start date Start date
D

Dow

I am having trouble finding what I am looking for. Hopefully someone
out there can assist. I suspect it is easier than I am making it.

My goal is to find the blank cells in a column and concatenate three
corresponding cells. The trouble I am having is making sure the
formula I use corresponds to the correct row.

I have written a macro to select the empty cells in Column A. I then
want to insert a formula similar to "=B1&C1&D1" but I need the row
number to corrospond to the row number of the Cell in column A.

If A1 is blank this formula works perfectly, it fills in the blanks
and it changes to B2, C2, D2, etc as needed. A1 will not always be
blank though. If A3 is the first blank cell it fills in the
information for B1, C1, and D1 and then fills down from there which
makes sense based on the formula, but this is not what I need it to
do.

If A1 is not the first empty cell what is the formula or VBA code to
use so Excel sees the cell and combines the corresponding cells from
B, C, and D?

I am sure I will feel like an idiot when I see it but for some reason
the solution is escaping me right now (too many late nights I am
sure). I have looked at row(), column(), address() and tried some
combining and such to get the results I want. It has not worked.

Thanks for everything.

Dow.
 
Hi,

Try this

Sub Sistence()
For counter = 2 To 20
If Range("D" & counter) = "" Then
Range("D" & counter).Formula = "=A1&B1&c1"
End If
Next
End Sub

Mike
 
OOPS,

I had an elderly moment, try this instead

Sub Sistence()
For counter = 2 To 20
If Range("D" & counter) = "" Then
Range("D" & counter).Formula = _
"=A" & counter & "&B" & counter & "&c" & counter
End If
Next
End Sub

Mike
 
OOPS,

I had an elderly moment, try this instead

Sub Sistence()
For counter = 2 To 20
    If Range("D" & counter) = "" Then
      Range("D" & counter).Formula = _
      "=A" & counter & "&B" & counter & "&c" & counter
    End If
Next
End Sub

Mike







- Show quoted text -
Click to expand...

Did not quite work but I think I can modify it. For my own
information is there a formula that can be written (non VBA) that
would help Excel see the location of the cell and use the correct row
number?
 
Back
Top