Avoiding nested If statements

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
213
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

rollis13

Well-known Member
Joined
Jul 30, 2012
Messages
1,035
Office Version
  1. 2016
Platform
  1. Windows
Could be something like this. See if you can adapt it to your project and make it even more dinamic:
VBA Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
19,140
Office Version
  1. 2013
Platform
  1. Windows
Maybe this will work. Not sure
VBA Code:
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

Well-known Member
Joined
Aug 28, 2020
Messages
1,035
Office Version
  1. 365
Platform
  1. Windows
Try this

VBA Code:
If Application.CountIf(cell.Resize(4, 1), "string1") = 4 Then
 
Solution

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,140
Office Version
  1. 2013
Platform
  1. Windows
What might these values look like?
are they numbers or strings Like "Alpha" "Bravo"

Try this if values like "Apple"

VBA Code:
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
 
Last edited:

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
213
Office Version
  1. 365
Platform
  1. Windows
Try this

VBA Code:
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

Well-known Member
Joined
Aug 28, 2020
Messages
1,035
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,176,634
Messages
5,904,156
Members
435,074
Latest member
McKay_S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top