Forum Discussion

csigman's avatar
csigman
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 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. 

 

 

excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    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.

7 Replies

  • HansVogelaar's avatar
    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's avatar
      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's avatar
        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

Resources