Need help inserting formula with macro
 

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!F 2,""

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

Need help inserting formula with macro
 

not sure if this is what you want or not

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

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

--


Gary


"Judy Ward" wrote in message
...
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!F 2,""

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

Need help inserting formula with macro
 

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" wrote:

not sure if this is what you want or not

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

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

--


Gary


"Judy Ward" wrote in message
...
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!F 2,""

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

Need help inserting formula with macro
 

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

=IF(NotificationFormat!F2<"",NotificationFormat!F 2,"")

post back the formula you want.

--


Gary


"Judy Ward" wrote in message
...
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" wrote:

not sure if this is what you want or not

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

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

--


Gary


"Judy Ward" wrote in message
...
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!F 2,""

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

Need help inserting formula with macro
 

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<"""",NotificationForma t!F2,"""")"
Lrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A2:A" & Lrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 11 Aug 2008 16:58:02 -0700, Judy Ward
wrote:

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

Need help inserting formula with macro
 

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.

"Gary Keramidas" wrote:

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

=IF(NotificationFormat!F2<"",NotificationFormat!F 2,"")

post back the formula you want.

--


Gary


"Judy Ward" wrote in message
...
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" wrote:

not sure if this is what you want or not

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

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

--


Gary


"Judy Ward" wrote in message
...
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!F 2,""

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