Looping through a range and inserting a formula where condition met

I am having trouble looping through a range and inserting a formula where a condition is met.

My range is "h9:i"

My current code inserts a formula in column i when there is a value in the adjacent row in column h:

Please Login or Register  to view this content.

However, because there are so many rows (12,000+) it is taking a considerable length of time to run.

Some of the cells in column i will already contain the relevant formula, so to speed this up I want to insert the formula in column i only when the cell value (in column i) is not null, as opposed to inserting it for all cells within the range.

I know how to loop through the range, but the problem is the 'H9' cell reference in the formula in the code above will need to change depending on what row it is being inserted into- so if it is going into "i31" this will need to be "h31".

This is the bit I am stuck on.

Hope that makes sense...any ideas?

Strud

yeah it was to insert where they are blank.

So your edit works perfectly, speeds it up massively.

Thanks for your help!

Yeah I know it wasn't looping, I thought looping would be the way to solve it, but .SpecialCells(xlCellTypeBlanks) works a treat

Cheers!

done.

cheers.