Problem Applying Formula to cell

  • Thread starter Thread starter Graham
  • Start date Start date
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
 
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) & ")"
 
Back
Top