Do Until Loop Code

[csigman]

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 should be a relatively quick Do Until code, but unsure how to create it. 

Below is a quick example. 

Starting at row 6, populate column A with F2 until B="Entity_ID". After that, populate column A with F3 until the end of the data set. 

[#2 · Accepted solution]

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

[#3]

Thank you!

[#4]

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?

[#5]

@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

[#6]

@Hans Vogelaar 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?

[#7]

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

[#8]

Thank you!