Hi
I have the following bit of code that is supposed to find all blank cells in a column, insert a vlookup formula and then if the formula returns 0, leave the cell blank, otherwise replace the formula with the found value.
When I type the vlookup code in manually in the same way, it works fine. When I insert it using this method, the vlookup doesn't find anything. Please could someone have a look and advise why it might not be working
Dim cell As Range, rng As Range
With Worksheets("All Data")
For Each cell In .Range("E3:E" & row)
If cell.Value = "" Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell
End With
rng.Select
Selection.FormulaR1C1 = "=VLOOKUP([@[Job Number]],'\\network folder name\[Cinram PODs.xlsm]Cinram PODs'!R5C1:R1000C4,2,FALSE)"
With rng
If cell.Value = 0 Then .Value = "" Else: .Value = .Value
End With
Bookmarks