Create Macro to copy a formula to range of cell

[Guest]

Excel 2000. I am a novice to this and intend to create a macro to copy a
fixed formula to a range of cell by a click of a button.

Below is the marco created but was prompted with error message when I run it.

Error message reads "Run-time error "5" : Invalid procedure cell or
argument" on the row starting with "Application.WorksheetFunction.IF....".
Please help.

Sub Macro2()
'Public strModule As String
' Macro2 Macro

Dim IsNumber As Variant
Dim Search As Variant

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A10").Select
Application.WorksheetFunction.IF(IsNumber(Search("TOTAL", C16)),
(Right(Left(C16, Len(C16) - 1), 5)), "").Paste

Range("A10").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=200
Range("A10:A200").Select
ActiveSheet.Paste
End Sub

 

[AD108]

Hi,

No sure exactly what you need to do from your code, but see the part I
replaced. You don't want to use the "application.worlsheetfunction" method.
That is for when you want to use an excel function to manipulate a value in
your code.

Hopefully this will get you on the right track.

Sub Macro2()
'Public strModule As String
' Macro2 Macro

Dim IsNumber As Variant
Dim Search As Variant

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A10").Select


ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(SEARCH(""TOTAL"", R[11]C[-1])),(RIGHT(LEFT(R[11]C[-1],
LEN(R[11]C[-1]) - 1), 5)), """")"


Range("A10").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=200
Range("A10:A200").Select
ActiveSheet.Paste
End Sub

 

[Guest]

I wasn't specific.
Actually needs to copy a "If" formula to the cell when the formula search
and found the word "Total" in column C
I have tried your advise but couldn't get the formula pasted on the cell.
Your help again please.

Hi,

No sure exactly what you need to do from your code, but see the part I
replaced. You don't want to use the "application.worlsheetfunction" method.
That is for when you want to use an excel function to manipulate a value in
your code.

Hopefully this will get you on the right track.

Sub Macro2()
'Public strModule As String
' Macro2 Macro

Dim IsNumber As Variant
Dim Search As Variant

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A10").Select


ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(SEARCH(""TOTAL"", R[11]C[-1])),(RIGHT(LEFT(R[11]C[-1],
LEN(R[11]C[-1]) - 1), 5)), """")"


Range("A10").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=200
Range("A10:A200").Select
ActiveSheet.Paste
End Sub

Excel 2000. I am a novice to this and intend to create a macro to copy a
fixed formula to a range of cell by a click of a button.

Below is the marco created but was prompted with error message when I run
it.

Error message reads "Run-time error "5" : Invalid procedure cell or
argument" on the row starting with "Application.WorksheetFunction.IF....".
Please help.

Sub Macro2()
'Public strModule As String
' Macro2 Macro

Dim IsNumber As Variant
Dim Search As Variant

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A10").Select
Application.WorksheetFunction.IF(IsNumber(Search("TOTAL", C16)),
(Right(Left(C16, Len(C16) - 1), 5)), "").Paste

Range("A10").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=200
Range("A10:A200").Select
ActiveSheet.Paste
End Sub