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" <(E-Mail Removed)> wrote in message
news:5DE8593B-4E99-4F61-ACF7-(E-Mail Removed)...
> 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
>
>
|
Similar Threads
