VBA show cell address from match function

[Deimos]

Hi,

I'm a first time poster. I'm creating a sub to search a range and locate a cell that contains the same first two letters of a string. Once located I want to show the cell address in a message box. The below code gives the Run time error '91': Object variable or with block variable not set. The line that receives the error is in bold below. I'm unsure where i've gone wrong, could someone please assist me.

Sub testmatch()

Dim extractedtext As String
Dim r As Integer
Dim c As Integer
Dim region As Range

r = 2
c = 2

Do

extractedtext = Sheet2.Cells(r, c).Value

region = Application.Match(extractedtext & "*", Sheet1.Range("C4:R27"), 0)
If Not region Is Nothing Then
MsgBox extractedtext & "Found at" & region
End If

r = r + 1

Loop Until Sheet2.Cells(r, c).Value = ""

End Sub

 

[ukmikeb]

Hi

Welcome to the Forum.

Firstly, the MATCH function returns a row or column number relative to the start of the Range being searched. It does not return an Address.

Additionally, MATCH will only give you the first occurence.

Here is an alternative approach using the FIND method -

Sub testmatch()

Dim extractedtext As String
Dim r As Integer
Dim c As Integer
Dim x As Variant
 
r = 2
c = 2
 
Do
 
extractedtext = Sheets("Sheet2").Cells(r, c).Value
 
With Sheets("Sheet1").Range("C4:R27")
    Set x = .Find(extractedtext, LookIn:=xlValues)
    If Not x Is Nothing Then
        firstAddress = x.Address
        Do
            MsgBox extractedtext & " Found at " & x.Address
            Set x = .FindNext(x)
        Loop While Not x Is Nothing And x.Address <> firstAddress
    End If
End With

r = r + 1
 
Loop Until Sheets("Sheet2").Cells(r, c).Value = ""
'
End Sub

It should provide you with all instances where the extracted text is located as the 1st 2 characters.

hth