Apr 08 2022 08:41 AM
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.
Apr 08 2022 08:55 AM
SolutionFor 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.
Apr 13 2022 08:22 AM
Apr 13 2022 08:36 AM
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
Apr 13 2022 01:50 PM
@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?
Apr 13 2022 02:23 PM
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.
Apr 08 2022 08:55 AM
SolutionFor 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.