CODEWITHSUNDEEP
excelvba
juliettebernard
juliettebernard

Reputation: 23

I am having trouble with a loop using VBA

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

Upvotes: 1

Views: 40

Answers (2)

VBasic2008
VBasic2008

Reputation: 54767

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

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

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

Upvotes: 1

Related Questions