Forum Discussion

Copper Contributor

Apr 08, 2022

Solved

Do Until Loop Code

Hello, I'm trying to create a Do Until code in VBA that will populate a certain value in column A until a criteria is met in column B, after which it will populate a second value. I think this s...

Apr 08, 2022

For example:

Sub FillA()
    Dim r As Long
    Dim m As Long
    Dim s As String
    Application.ScreenUpdating = False
    s = Range("F2").Value
    m = Range("B" & Rows.Count).End(xlUp).Row
    For r = 6 To m
        If Range("B" & r).Value = "Entity_ID" Then
            s = Range("F3").Value
        End If
        Range("A" & r).Value = s
    Next r
    Application.ScreenUpdating = True
End Sub

You could also use a formula. In A6:

=IF(COUNTIF(B$6:B6,"Entity_ID"),$F$3,$F$2)

Double-click the fill handle of A6 to fill down.

HansVogelaar

MVP

Apr 08, 2022

csigman

For example:

Sub FillA()
    Dim r As Long
    Dim m As Long
    Dim s As String
    Application.ScreenUpdating = False
    s = Range("F2").Value
    m = Range("B" & Rows.Count).End(xlUp).Row
    For r = 6 To m
        If Range("B" & r).Value = "Entity_ID" Then
            s = Range("F3").Value
        End If
        Range("A" & r).Value = s
    Next r
    Application.ScreenUpdating = True
End Sub

You could also use a formula. In A6:

=IF(COUNTIF(B$6:B6,"Entity_ID"),$F$3,$F$2)

Double-click the fill handle of A6 to fill down.

csigman

Copper Contributor

Apr 13, 2022

Hello - I now have a situation where I need to update the state name to a new name every time I reach the "entity_ID" cell. There could be up to 8 different states that need to be populated down column A. Is this achievable with the formula?

HansVogelaar
MVP
Apr 13, 2022
csigman

Use this version of the macro:

Sub FillA() Dim r As Long Dim m As Long Dim t As Long Dim s As String Application.ScreenUpdating = False t = 2 s = Range("F" & t).Value m = Range("B" & Rows.Count).End(xlUp).Row For r = 6 To m If Range("B" & r).Value = "Entity_ID" Then t = t + 1 s = Range("F" & t).Value End If Range("A" & r).Value = s Next r Application.ScreenUpdating = True End Sub
- csigman
  Copper Contributor
  Apr 13, 2022
  HansVogelaar Thank you, I'm getting an error when I try to run the code (Run-time error 1004 Application defined or object defined error) at the line beginning with "For r". I did update the columns to align with the data locations and ensure the excel is macro enabled. Attached the excel I'm working in. I'm not sure what is causing it, do you have any ideas?
  Do Until Loop.xlsx9 KB
  - HansVogelaar
    MVP
    Apr 13, 2022
    csigman
    
    You attached a .xlsx workbook, so it wasn't macro-enabled.
    
    The layout of the worksheet is different from that in your first post, so the macro has to be updated to match the layout.
    
    See the attached .xlsm workbook.
    Do Until Loop.xlsm14 KB