I am having trouble with a loop using VBA

user_12227051 0
Viewed 34

I am trying to make a code to loop the column b and then fill column c based on if its empty or not but its not working when I set the data into the middel of the excel sheet

Sub FillCellFromAbove()
Dim x As Integer
Dim y As Integer
y = Application.WorksheetFunction.Count(Range("B:B")) + 1

For x = 1 To y
    Range("C3:C7" & x).Select
    If Range("B" & x) = "" Then
        ActiveCell.Value = "Yes"
    ElseIf Range("B" & x) <> "" Then
        ActiveCell.Value = "NO"
    End If
Next x
End Sub

enter image description here

vba excel
2 Answers

Range("C3:C7" & x).Select is selecting a range starting at C3 and ending at C71 for the first loop and C72 for the second. Doubt that is what you want.

Also COUNT only counts the cells with numbers not the cells in a range from the first cell with a value to the last. So in this case you would return 4 and do 4 loops. Use:

Sub FillCellFromAbove()
    Dim x As Long
    Dim y As Long
    With ActiveSheet 'Better to set actual sheet
        y = .Cells(.Rows.Count,2).End(XlUp).Row
        For x = 3 To y
            If .Cells(x,2) <> "" Then
                .Cells(x,3) = "Yes"
            Else
                .Cells(x,3) = "No"
            End If
        Next x
    End With
End Sub
user_48515900

Flag Empty and Non-Empty Using Evaluate

Sub FlagEmptyNonEmpty()
    
    Const SourceColumn As String = "B"
    Const DestinationColumn As String = "C"
    Const YesFlag As String = "Yes"
    Const NoFlag As String = "No"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    With Intersect(ws.UsedRange, ws.Columns(SourceColumn))
        .EntireRow.Columns(DestinationColumn).Value _
            = ws.Evaluate("IF(ISBLANK(" & .Address(0, 0) & "),""" _
            & YesFlag & """,""" & NoFlag & """)")
    End With

End Sub
user_98140690
Related