Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Inserting a formula | Excel Discussion (Misc queries) | |||
a macro for inserting | Excel Programming | |||
a macro for inserting | Excel Programming | |||
macro for inserting a row | Excel Programming | |||
Inserting A Row Macro | Excel Programming |