Issue with Formula not following down the column in VBA macro

[mDgo0d4mE]

Hello,
i have a spreadsheet that i would like to add a column to "E" and name it BUCKETS as the header.
then i want it to copy a formula down from e2 down to lastrow that related to the information in column D

The issue im running into is that the row numbers are not changing in the formula and its putting quotes between the cell name 'D2' making the formula #NAME ?

[TABLE="width: 500"]
<tbody>[TR]
[TD]COLUMN D[/TD]
[TD]COLUMN E[/TD]
[/TR]
[TR]
[TD]FRUIT[/TD]
[TD]BUCKETS[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]FRUIT BUCKET[/TD]
[/TR]
[TR]
[TD]BANANA[/TD]
[TD]FRUIT BUCKET[/TD]
[/TR]
[TR]
[TD]BEAN[/TD]
[TD]VEGGIE BUCKET[/TD]
[/TR]
[TR]
[TD]CARROT[/TD]
[TD]VEGGIE BUCKET[/TD]
[/TR]
</tbody>[/TABLE]

any imput would be great, thanks in advance

Sub test()

Dim x As Long
Dim Rng As Range
Range("E1").EntireColumn.Insert shift:=xlToRight
Range("e1").Value = "BUCKETS"
Set Rng = Range("E2:E" & Range("A2").End(xlDown).Row)
Rng.FormulaR1C1 = "=IF(D2=""APPLE"",""FRUIT BUCKET"",IF(D2=""BANANA"",""FRUIT BUCKET"",IF(D2=""BEAN"",""VEGGIE BUCKET"",IF(D2=""CARROT"",""VEGGIE BUCKET""))))"
End Sub

 

[Fluff]

try

Rng.FormulaR1C1 = "=IF(rc4=""APPLE"",""FRUIT BUCKET"",IF(rc4=""BANANA"",""FRUIT BUCKET"",IF(rc4=""BEAN"",""VEGGIE BUCKET"",IF(rc4=""CARROT"",""VEGGIE BUCKET""))))"

 

[mDgo0d4mE]

This worked perfectly, thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback