VBA error

[paroduso]

Hi everyone...

im trying this simple vba code but im getting an error...

Method 'Formula' of object 'Range' failed

MY EXAMPLE:
cell A1=0,125 B1=1025 C1=1,016 D1=1000 and in cell "E4" will return = A1/B1*C1*D1 but i only want the calculations to be made when in cell "E4" IS NOT BLANK SO , when i type in cell "E2" THE MACRO SHOULD RUN...BUT ONLY WORKS WITH A BOTTON...

Range("E1").Formula = "=RC[-4]/RC[-3]*RC[-2]*RC[-1]"

THANK YOU.

 

[Fluff]

It needs to be FormulaR1C1.

 

[paroduso]

THIS IS MY MACROS

Sub CAL()

Range("E1").Formula = "=RC[-4]/RC[-3]*RC[-2]*RC[-1]"

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("E4").Value <> "" Then

Call CAL

End If

End Sub

IF I ADD THE "CAL" MACRO TO A BOTTON THE MACRO WORKS FINE...BUT WHEN I PUT THIS LINE OF COMMAND ON SHEET1

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("E4").Value <> "" Then

Call CAL

End If

End Sub

ERROR MESSAGE APEARS AND SOMETIMES EXCEL RESTART.

 

[Fluff]

Did you try what I suggested?

 

[paroduso]

HI Fluff...I hadn't seen your answer...I've tried it but it's still the same

 

[Fluff]

I see no reason why you would get a "Method 'Formula' of object 'Range' failed" error on that line, although with the code you have posted you will get a permanent loop.

 

[paroduso]

I see no reason why you would get a "Method 'Formula' of object 'Range' failed" error on that line, although with the code you have posted you will get a permanent loop.

the Excel executes the macro...but the message always appears.

 

[Michael M]

Where are you putting the CAL macro...try it in the same module as the change event

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E4").Value <> "" Then
Application.EnableEvents = False
Call CAL
Application.EnableEvents = False
End If
End Sub

 

[paroduso]

Where are you putting the CAL macro...try it in the same module as the change event

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E4").Value <> "" Then
Application.EnableEvents = False
Call CAL
Application.EnableEvents = False
End If
End Sub

thanks Michael works great...many thanks