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 ?

COLUMN D	COLUMN E
FRUIT	BUCKETS
APPLE	FRUIT BUCKET
BANANA	FRUIT BUCKET
BEAN	VEGGIE BUCKET
CARROT	VEGGIE BUCKET

<tbody>
</tbody>

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