=RC[-1]-RC[-4] Error when running Date Macro

[Donal28]

Hi All

I am getting an =RC[-1]-RC[-4] in the I:I fields when running the Macro below. Basically the macro added the date 25/02/2011 to the H:H field and then subtracts the date in the E:E from H:H with the result appearing in I:I. Not sure why it coming back with =RC[-1]-RC[-4] as I would expect the calcultation to appear as a number of days for example 132

Any help on this would be very much appreciated

Regards
Donal


Sub Jobcard_Days()
'
' Jobcard_Days Macro
'
'
Range("A1:G1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("H3").Select
Selection.NumberFormat = "@"
ActiveCell.FormulaR1C1 = "25/.2/2011"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("H3").Select
ActiveCell.FormulaR1C1 = "25/02/2011"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("H3").Select
Selection.AutoFill Destination:=Range("H3:H2620"), Type:=xlFillCopy
Range("H3:H2620").Select
ActiveWindow.ScrollRow = 2575
ActiveWindow.ScrollRow = 2564
ActiveWindow.ScrollRow = 2529
ActiveWindow.ScrollRow = 2380
ActiveWindow.ScrollRow = 2081
ActiveWindow.ScrollRow = 1792
ActiveWindow.ScrollRow = 1535
ActiveWindow.ScrollRow = 1267
ActiveWindow.ScrollRow = 985
ActiveWindow.ScrollRow = 697
ActiveWindow.ScrollRow = 394
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 1
Range("I3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-4]"
Range("I3").Select
Selection.AutoFill Destination:=Range("I3:I2620"), Type:=xlFillDefault
Range("I3:I2620").Select
End Sub

 

[RoryA]

Try changing this:

Range("I3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-4]"
Range("I3").Select
Selection.AutoFill Destination:=Range("I3:I2620"), Type:=xlFillDefault

to this:

With Range("I3")
   .Numberformat = "General"
   .FormulaR1C1 = "=RC[-1]-RC[-4]"
   .AutoFill Destination:=Range("I3:I2620"), Type:=xlFillDefault
End With

 

[Donal28]

Yes thats worked..thanks so much for your help