Need help inserting formula with macro

[Judy Ward]

I have a workbook with several worksheets. The "MergeList" sheet relies on
information from the "NotificationFormat" sheet. Here is the formula used in
cell A2 of "MergeList":
=IF(NotificationFormat!F2<>"",NotificationFormat!F2,""

This formula is copied and pasted to the rest of the cells in the column so
that the row number increments.

A copy of this workbook is used over and over again and somehow this formula
gets messed up or deleted completely. I would like to have a "reset" macro
that inserts this formula in cell A2 and copies and pastes it to the rest of
the cells in the column.

I can't even get the part the inserts the formula to work:
Sheets("MergeList").Select
With ActiveSheet
.Range("A2").FormulaR1C1 =
"=IF(NotificationFormat!F2<>'',NotificationFormat!F2,'')"
End With

I get "Run-time error '1004': Application-defined or object-defined error"

I would appreciate any help you can give me.

Thank you,
Judy

 

[Gary Keramidas]

not sure if this is what you want or not

With Sheets("MergeList")
.Range("A2").Formula =
"=IF(NotificationFormat!F2<>"""",NotificationFormat!F2,"""")"
End With

post back which column you determine where the last row is.

 

[Judy Ward]

Gary,

Thank you for replying, but this didn't work. It didn't give me a runtime
error, but the formula gets inserted with single quotes around the cell:
=IF(NotificationFormat!'F2'<>"",NotificationFormat!'F2',"")

And the displayed value is "#NAME?"

Any other ideas?
Judy

 

[Gary Keramidas]

not sure what you tried, but my code enters this formula:

=IF(NotificationFormat!F2<>"",NotificationFormat!F2,"")

post back the formula you want.

 

[Gord Dibben]

I will assume that column B has a last cell and you will fill from A2 to
last cell in B

Sub fill()
Dim Lrow As Long
With Sheets("MergeList")
.Range("A2").Formula = _
"=IF(NotificationFormat!F2<>"""",NotificationFormat!F2,"""")"
Lrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A2:A" & Lrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP

 

[Judy Ward]

You are right, this formula does work. The error must have been coming from
the other worksheet (and me trying to do two things at once and being
interrupted here at work).

Thank you again, this solves my problem.