Results 1 to 8 of 8

Thread: VBA Do While loops

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Do While loops

    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!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Do While loops

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Dec 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Do While loops

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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Do While loops

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Dec 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Do While loops

    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?

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Do While loops

    Will you still be using col H for the last row?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Dec 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Do While loops

    Quote Originally Posted by Fluff View Post
    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

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Do While loops

    Glad you sorted it & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •