Formula
 

Thanks for your help with my last prob Frank....

The below code should check if there is a formula in range
b2 on sheet email and if it doesnt fill range b2:d125 with
formulas. NB I have ommited formula as they are rather
long and make code unclear.

This code works if I have worksheet Emails open but I
would like to run this code from another sheet. I
previously used worksheets("email").select but I do not
want the user to see this sheet.

Does anyone know why the with worksheets("email") is not
working?

Sub Add_Email_Formulas()

If Worksheets("Email").Range("B2").HasFormula Then
End
Else

With Worksheets("Email")
Range("B2").Formula = "FORMULA"
Range("C2").Formula = "FORMULA"
Range("D2").Formula = "FORMULA"
Range("B2:D2").AutoFill Destination:=Range("B2:D125")
End With
End If

End Sub

Formula
 

You have the syntax a little wrong. Your code

With Worksheets("Email"
Range("B2").Formula = "FORMULA
Range("C2").Formula = "FORMULA
Range("D2").Formula = "FORMULA
Range("B2:D2").AutoFill Destination:=Range("B2:D125"
End Wit

Should be

With Worksheets("Email"
..Range("B2").Formula = "FORMULA
..Range("C2").Formula = "FORMULA
..Range("D2").Formula = "FORMULA
..Range("B2:D2").AutoFill Destination:=Range("B2:D125"
End Wit

You need the period to indicate it belongs to the Worksheets("Email"). Without the period indicating the range is qualified by the With statement, it's trying to use the Activesheet

-Brad

Formula
 

Hi

Thanks Brad, unfortunately the code is now falling over on
the line
..Range("B2:D2").Autofill Destination:=Ranhe("B2:D125")
It comes up with the error Autofill Method of Range Class
Failed

DO you have any idea why this is happening?


-----Original Message-----
You have the syntax a little wrong. Your code:

With Worksheets("Email")
Range("B2").Formula = "FORMULA"
Range("C2").Formula = "FORMULA"
Range("D2").Formula = "FORMULA"
Range("B2:D2").AutoFill Destination:=Range("B2:D125")
End With

Should be:

With Worksheets("Email")
..Range("B2").Formula = "FORMULA"
..Range("C2").Formula = "FORMULA"
..Range("D2").Formula = "FORMULA"
..Range("B2:D2").AutoFill Destination:=Range("B2:D125")
End With

You need the period to indicate it belongs to the
Worksheets("Email"). Without the period indicating the
range is qualified by the With statement, it's trying to
use the Activesheet.

-Brad
.

Formula
 

Well, first you need a single period, not two preceding the first range. Second, you misspelled Range in the Destination argument. Third, do you mean to use the ActiveSheet range with the destination? You probably want to use your With statement again, so precede the Destination range with a period also

-Bra

----- Jahsonn wrote: ----

H

Thanks Brad, unfortunately the code is now falling over on
the lin
..Range("B2:D2").Autofill Destination:=Ranhe("B2:D125"
It comes up with the error Autofill Method of Range Class
Faile

DO you have any idea why this is happening


-----Original Message----
You have the syntax a little wrong. Your code
With Worksheets("Email"
Range("B2").Formula = "FORMULA
Range("C2").Formula = "FORMULA
Range("D2").Formula = "FORMULA
Range("B2:D2").AutoFill Destination:=Range("B2:D125"
End Wit
Should be
With Worksheets("Email"
..Range("B2").Formula = "FORMULA
..Range("C2").Formula = "FORMULA
..Range("D2").Formula = "FORMULA
..Range("B2:D2").AutoFill Destination:=Range("B2:D125"
End Wit
You need the period to indicate it belongs to the
Worksheets("Email"). Without the period indicating the
range is qualified by the With statement, it's trying to
use the Activesheet
-Bra