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:

Please Login or Register  to view this content.

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