Avoiding nested If statements

[Luke777]

Hi all,

Is there a better way of doing the following that avoids nesting if statements? Basically I'm building up a bit of code - prior to this I'm given a row number. I then check the row number for "string1". Where I find "string1" I would also like to check if the three cells below that address also contain "string1".

which would look at little like

If Cell.value="string1" and cell.offset(1,0).value="string1" and cell.offset(2,0).value="string1" and cell.offset(3,0).value="string1" then...

But that seems a very long-winded method for checking related cells. I'm looking for a method where I can easily specify the number of offset equivalents to check (e.g. 5 would check 5 cells below (or including) the original cell)

Thanks all!

 

[rollis13]

Could be something like this. See if you can adapt it to your project and make it even more dinamic:

Option Explicit
Sub test()
    Dim string1 As String
    Dim rowOffset As Long
    Dim cell   As Range
    Dim x      As Long
    Dim found  As Long
    Set cell = ActiveCell
    string1 = "string to be searched"             '<- could be a cell value
    rowOffset = 5                                 '<- could be a cell value
    If cell.Value = string1 Then
        For x = 1 To rowOffset
            If cell.Offset(x, 0).Value = string1 Then
                found = found + 1
            End If
        Next x
    End If
    If found = rowOffset Then
        '...
        'do something
        '...
    End If
End Sub

 

[My Aswer Is This]

Maybe this will work. Not sure

Sub My_Script()
'Modified 1/30/2022  5:43:56 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        Select Case Cells(i, 1).Value
            Case 1 To 20
                Cells(i, 5).Value = "Alpha"
        End Select
    Next
Application.ScreenUpdating = True
End Sub

 

[kevin9999]

Try this

If Application.CountIf(cell.Resize(4, 1), "string1") = 4 Then

 

[My Aswer Is This]

What might these values look like?
are they numbers or strings Like "Alpha" "Bravo"

Try this if values like "Apple"

Sub My_Script_New()
'Modified  1/30/2022  8:32:04 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        Select Case Cells(i, 1).Value
            Case "Alpha", "Bravo", "Charlie", "Apple", "Orange"
                Cells(i, 5).Value = "Yes"
        End Select
    Next
Application.ScreenUpdating = True
End Sub

 

[Luke777]

Try this

If Application.CountIf(cell.Resize(4, 1), "string1") = 4 Then

initial testing of this one seems to work - which is surprising given how short it is compared to the other solutions. If I'm reading it correctly, the first "4" is the number of cells in the column of the starting location. "1" is the column size, so just the one column I'm looking in. The second 4 is saying that "string1" needs to appear four times I think

 

[kevin9999]

initial testing of this one seems to work - which is surprising given how short it is compared to the other solutions. If I'm reading it correctly, the first "4" is the number of cells in the column of the starting location. "1" is the column size, so just the one column I'm looking in. The second 4 is saying that "string1" needs to appear four times I think

That's right Luke, the first 4 is the number of cells to check- the original and the 3 below the original (you can easily change this) by 1 column wide. The second 4 means it needs to find the string a total of 4 times, in other words, in all 4 cells.