VBA Do While loops

dan8825

New Member
Joined
Dec 24, 2018
Messages
26
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.

Code:
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.

Code:
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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Upvote 0
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..
 
Upvote 0
How about
Code:
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
 
Upvote 0
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?
 
Upvote 0
Will you still be using col H for the last row?
 
Upvote 0
Glad you sorted it & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top