PDA

View Full Version : Solved: Insert a formula if a different cell is not blank



JimS
06-04-2009, 04:51 PM
Can a Macro insert/write a Formula into a cell based on a different cell in the same row not being blank.

In example, if Cell D2 has anything then Cell H2 needs to have =Concatenate(B2,A2,C2).

The macro would have review each row and write the same formula in Column H but adjusted to match the particular row number.

Thanks...

Jim

mikerickson
06-04-2009, 07:01 PM
Perhaps something like this
Sub test()
With ThisWorkbook.Sheets("sheet1")
On Error Resume Next
With Application.Intersect(.Range("H:H"), .Range("D:D").SpecialCells(xlCellTypeConstants).EntireRow)
.FormulaR1C1 = "=CONCATENATE(RC2,RC1,RC3)"
End With
With Application.Intersect(.Range("H:H"), .Range("D:D").SpecialCells(xlCellTypeFormulas).EntireRow)
.FormulaR1C1 = "=CONCATENATE(RC2,RC1,RC3)"
End With
On Error GoTo 0
End With
End Sub

JimS
06-05-2009, 05:11 AM
Thanks, is there a way that the concatenate can do (add / between the 3 entries)?

=concatenate(RC2," / ", RC1," / ",RC3)

Jim

mikerickson
06-06-2009, 06:53 AM
That looks like it might work if the " are doubled inside the string.