Problem Applying Formula to cell

G

Graham

Hi

I have a problem a with macro I have recorded/written.
The macro applies a formula to a range of cells which references values in a
column in a separate worksheet and returns cell contents from this worksheet
then deletes blank rows.

The macro runs as expected when the formula has been entered manually in the
starting cell but as soon as i try to apply the formula as part of the macro
(commented out second line) it returns error code 1004.




Range("A3").Select
' ActiveCell.FormulaR1C1 = "=IF('Raw data'!$I1-25 =TODAY(),'Raw
data'!A1,"")"
Selection.AutoFill Destination:=Range("A3:N3"), Type:=xlFillDefault
Range("A3:N3").Select
Selection.AutoFill Destination:=Range("A3:N65000"), Type:=xlFillDefault
Range("A3:N65000").Select

ActiveSheet.UsedRange.Select
X = ActiveSheet.UsedRange.Columns.Count
Selection.AutoFilter
For i = 1 To X
Selection.AutoFilter Field:=i, Criteria1:="="
Next
X = ActiveSheet.UsedRange.Count
Range("A2:G" & X & "").Delete
ActiveSheet.AutoFilterMode = False

Range("i3:i65000").Select
Range("i3:i65000").Activate
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Select


End Sub




Any help would be much appreciated

Graham
 
G

Gary Brown

The " will get you every time BUT Chr(34) = " so....

ActiveCell.Formula = _
"=IF('Raw data'!$I1-25 =TODAY(),'Raw data'!A1," & _
Chr(34) & Chr(34) & ")"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top