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
csigman
Copper Contributor
Apr 09, 2022
Thank you!