Create Macro to copy a formula to range of cell
 

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

Create Macro to copy a formula to range of cell
 

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

"summer" wrote in message
...
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

Create Macro to copy a formula to range of cell
 

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.

"AD108" wrote:

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

"summer" wrote in message
...
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