Find exact strings while looping through a range using VBA

Question

I have the following code that goes through a table. When it finds the word "Reporting" in column C and "OSI" in column B, it creates a range called "OSIRep" going from column D-E-F for every row where the criteria in column B and C are true.

Set sht = ThisWorkbook.Worksheets("Features")


'Reporting and OSI

Set featuresRng = sht.Range(sht.Range("B1"), sht.Range("C" & sht.Rows.Count).End(xlUp))
rngArray = featuresRng
ReDim NewArr(1 To 1)
y = 1
For i = 1 To UBound(rngArray)
    If rngArray(i, 2) = "Reporting" And rngArray(i, 1) = "OSI" Then
        ReDim Preserve NewArr(1 To y)
        NewArr(y) = featuresRng.Rows(i).Resize(1, 3).Offset(0, 2).Address
        y = y + 1

    End If
Next i

sRng = Join(NewArr, Application.DecimalSeparator)
ThisWorkbook.Names.Add "OSIRep", sht.Range(sRng)

However, I have two problems with this: 1- When I change the word to "Security" and "OSI" to create a range "OSISec", I get an error and I don't know why. The error is 'Run-Time error 1004 - Method 'Range' of object '_Worksheet' failed' and this occurs on sht.Range(sRng)

The range it is searching through is no different and the name I am trying to create doesn't already exist. Any ideas?

2- When I change the strings to "Reporting" and "Search and Filter", the code creates a range where any cells containing the words "Search" or "and" or "Filter" are included in the range. How do I search for the EXACT string? I believe it uses Find and xlwhole? I am not sure how to use this though?

I have tried taking an protections of the worksheet, unlocking cells and exploring the error code, all to no avail.

Thank you in advance for any help or insight you can provide! I appreciate I am a complete beginner, so any help is greatly appreciated.

user_6414747 · Answer

I don't exactly understand the problem but maybe there are some issues with your files. When I use this test setup:

With this code:

Sub SOCode()
Set sht = ThisWorkbook.Worksheets("Features")

Set featuresRng = sht.Range(sht.Range("B1"), sht.Range("C" & sht.Rows.Count).End(xlUp))
rngArray = featuresRng

ReDim NewArr(1 To 1)
y = 1
For i = 1 To UBound(rngArray)
    If rngArray(i, 2) = sht.Range("F2") And rngArray(i, 1) = sht.Range("F1") Then
        ReDim Preserve NewArr(1 To y)
        NewArr(y) = featuresRng.Rows(i).Resize(1, 3).Offset(0, 2).Address
        y = y + 1

    End If
Next i

'delete all named ranges first
Dim nm As Name
For Each nm In ThisWorkbook.Names
    nm.Delete
Next nm

sRng = Join(NewArr, Application.DecimalSeparator)
ThisWorkbook.Names.Add sht.Range("F3").Value, sht.Range(sRng)

'delete output of named ranges from last test
Range("H2:H1000").ClearContents

'paste named ranges in col "H"
i = 2
For Each nm In ThisWorkbook.Names
    Cells(i, 8).Value = nm.Name
    i = i + 1
Next nm
End Sub

All possible strings you mentioned are working on my site.

I would create a fresh new file and test it again. I changed your code only in reading the possible strings from column F (OSI in F1, e.g. reporting =F2, rangeName=F3) and paste finally all created names to column H just for debugging. Remember that I get error when there isn't a combination found, as then creation of named range with an empty string in "sRng" results in an error of course.

Find exact strings while looping through a range using VBA

1 Answers