I want to loop through each cell in a column that is blank and look up a value from the neighbouring column from a data source. I want to do this in a loop. I have looked in many forums and can get this to work properly.

1 Answer
1

Here is a quick code block that I wrote to replicate what I think you're trying to get at.

I created some sample data. The lookup on the right has unit prices and on the left you have a table with some entries missing in the 'total price' column.

The code block below loops through the cells in column C and runs a vlookup each time it finds a blank cell.

Sub FillInBlanks()
Dim rng As Range
Set rng = ActiveSheet.Range("C2:C21")
For Each cell In rng
If IsEmpty(cell) Then
cell.FormulaR1C1 = "=VLOOKUP(RC[-2],R1C7:R11C8,2,FALSE)*RC[-1]"
End If
Next cell
End Sub