VBA Do While loops

[dan8825]

Hello,

Im looking for VBA to input a formula into a cell based on the cell value next to it. If the Cell value next to it is blank it shall stop.

I have the below which loops and stops when a blank cell is found, but I need it to perform an action.

Sub DoWhile_Loop()

Dim BlankFound As Boolean
Dim x As Long


'Loop until a blank cell is found in Column A
  Do While BlankFound = False
    x = x + 1
    
    If Cells(x, "A").Value = "" Then
      BlankFound = True
    End If
  Loop


'Report out first blank cell found in Column A
  MsgBox "Cell A" & x & " is blank!"
  
End Sub

The action would be the below lookup which populates in the next blank row, column L.

Mainbook.Sheets("People Value Tracker").Cells(Rows.Count, 12).End(xlUp).Offset(1, 0) = "=IFERROR(VLOOKUP(RC[3],'Tab 1'!C[-10]:C[-4],6,0),"""")"

So this lookup should go into Column L where there is data present in H, if no data is present it should stop putting the lookup in.


Thanks!

 

[Fluff]

Does that formula need to go in all cells in col L where col H has data?
If so what is the first row the formula needs to go in? and will col H have any blanks cells?

 

[dan8825]

The Formula will need to go in the next blank row in L (which is what my current look up does, the unknown is how far I need to 'drag' down the formula)

H will have names in, the formula should be listed as long as there are names in H.

I will add more names to H overtime then run the code to populate L etc..

 

[Fluff]

How about

Sub dan8825()
   With Mainbook.Sheets("People Value Tracker")
      .Range(.Range("L" & Rows.Count).End(xlUp).Offset(1), .Range("H" & Rows.Count).End(xlUp).Offset(, 4)).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[3],'Tab 1'!C[-10]:C[-4],6,0),"""")"
   End With
End Sub

 

[dan8825]

That is perfect!

I need to add two more of these into Column J and M, I have tried changing the L at the beginning but this does not do the trick... How does one amend the column it should go into?

 

[Fluff]

Will you still be using col H for the last row?

 

[dan8825]

Will you still be using col H for the last row?

Yes I would be, I have figured it out though, Just by changing the row at the beginning and the offset number how many columns away.

Thanks for all your help

 

[Fluff]

Glad you sorted it & thanks for the feedback