In VBA, I want to make a statement If for more than one cell in a range contains a value

advertisements

So I have the following VBA loop set up, but want to add a line that says "If there are two cells within this range that have a value, do this. If there are three cells within a range that have a value, do that." What I have so far is:

Sub Test1()

    Dim Rng As Range
    Dim i As Long
    i = 3
    Application.ScreenUpdating = True

    While i <= 133
        Set Rng = Range("C" & i)

        If Rng.Offset(, 2).Resize(, 7) <> "" Then
            Rng.Offset(, 1).FormulaR1C1 = "Blank"
            i = i + 1
        Else: Stop
        End If
    Wend

End Sub

So I have the VBA script print the word "Blank" into the appropriate cell if this range is empty. But how can I add more lines to say "If one cell in this range contains a value," or "if two cells in this range contain a value"


Here is how you can check if there are more than one non-empty cell in the given range:

If Application.WorksheetFunction.CountA(Rng.Offset(, 2).Resize(, 7)) > 1 Then


Few additional tips to your code:

  • If you know exactly the initial and final value of i you should use For ... Next loop instead of While ... Wend. So you could replace this code:

    i = 3
    '(...)
    
    While i <= 133
        '(...)
        i = i + 1
    
    Wend
    
    

    with this:

    For i = 3 To 133
        '(...)
    Next i
    
    
  • I think this line of code will cause Type mismatch error:

    If Rng.Offset(, 2).Resize(, 7) <> "" Then
    
    

    because you are trying to compare an object of Range type with a primitive value (empty string). To avoid this issue you can use the similar code as above:

    If Application.WorksheetFunction.CountA(Rng.Offset(, 2).Resize(, 7)) = 0 Then