Problem Applying Formula to cell
 

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

Problem Applying Formula to cell
 

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) & ")"

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Graham" wrote:

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

Problem Applying Formula to cell
 

Cheers Gary
Works like a charm, thanks for your help

Graham

"Gary Brown" wrote:

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) & ")"

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Graham" wrote:

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