Update Code to work with multiple rows

Hi all,

This will be my first post and I'm in quite a pickle. I've been wrecking my brain trying to work this problem out. Basically I want to use an IF statement to check Range 1 (about 1000 entries) and compare Range 2 (only 6 entries). Range 2 has the sizes that corresponds to a price, so the figures in Range 1 will be compared and output a price onto another cell. Here is a code below:

Dim i As Integer
Dim DU As Range
Dim rngDU As Range

Public Sub duct_rate()
Set rngDU = Sheets("Rates").Range("E5:E10")
Set GIRTH = Sheets("Duct Takeoff").Range("E9:E1009")
Set DUPRICE = Sheets("Duct Takeoff").Range("G9:G1009")

For Each DU In rngDU
    If Range("GIRTH").Value = Range("rngDU").Value Then
        Range("DUPRICE").Value = Round(DU.Offset(i, 2), 0)
    ElseIf DU < Range("E9:E1009") Then
        Range("G9:G1009") = Round(DU.Offset(i + 1, 2), 0)
    ElseIf Range("E9:E1009") < 1 Then
        Range("G9:G1009") = Round(Sheets("Rates").Range("G5"), 0)
    End If
Next

End Sub

I know between 'For' to the end is kinda wrong but I haven't had much luck on solving this and been googling for a solutions but still have no luck. If there is any advice much appreciated.

I have placed a sample workspace on how I would like it to work, basically the girth sizes are there, I just need them to be compared to the second sheet labelled "Rates" and the "supply and install rate"(column G), the VBA is there, I have changed a few things, the only concern is the "Calc_stuff" module which is eating me.

I would rather this on VBA, I wasnt too sure about the if statements on ranges and remembered some limitations using formulas. Also if its no biggie, you can move the thread to the appropriate section I assume "Excel Programming / VBA / Macros"?

calcs.xlsm

EDIT: the code works for Row 9 at the moment, I want to be able to do this for multiple rows and in future I can add more and the same processes can be applied again.
Cheers