Excel VBA - Loop Through Empty Cells and Get Value From Another

[Javanius]

Hi everybody, I am trying to learn Excel VBA on my own and I am a beginner. So I have a quick question.

I want to go to next empty cell of column F and insert the value of A8. In this case value of the red cell (F9) should be "111" and value of the green cell (H15) "222" and finally for the blue cell value should be "333". This table is dynamic, meaning total number of "111", "222", "333" etc. may change.

How can I do that with using variables and loop?
I couldn't do much but here is my code. As you can see value entered manually and there is no loop. I need to assign a variable for value which is equal to Row-1 of the colored cells and of course it should be done in loop. Running macros for each empty cells might be a bad idea ?

Dim NextEmptyCell as Long
NextEmptyCell = .Cells(Rows.Count, "F".End(xlUp).Row + 2
Range("F1:F" & NextEmptyCell).Find("").Value = "111"

Consider following screenshot.

 

[Joe4]

Welcome to the Board!

Try this:

Sub MyInsertValues()

    Dim lr As Long
    
'   Find last row in column F with data
    lr = Cells(Rows.Count, "F").End(xlUp).Row
    
'   Populate blank cells in column F with formula
    Range("F1:F" & lr + 1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C[-5]"
    
End Sub

 

[Javanius]

Welcome to the Board!

Try this:

Sub MyInsertValues()

    Dim lr As Long
   
'   Find last row in column F with data
    lr = Cells(Rows.Count, "F").End(xlUp).Row
   
'   Populate blank cells in column F with formula
    Range("F1:F" & lr + 1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C[-5]"
   
End Sub

Works like a charm! I didn't even know FormulaR1C1, thank you so much!

 

[Joe4]

Works like a charm! I didn't even know FormulaR1C1, thank you so much!

You are welcome!

If you turn on the Macro Recorder, and record yourself entering formulas in Excel, it will (by default) create all the formulas in VBA using the relative (R1C1) referencing.
The cool thing about that is if you aren't sure what the syntax you should be, you can just turn on the Macro Recorder and record yourself entering the formula in manually, and let Excel build it for you!
The Macro Recorder is a great little tool to easily and quickly get snippets of code like that.

By the way, you can actually also quickly/easily fill in those cells manually, using the technique described here: How to fill blank cells with value above / below / left / right in Excel?
The VBA code I created really is just performing those steps, with a few little twists (i.e. I extended the range one row down to get the last value we need included).