Finding Address of The 2nd Instance of a value in a range

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a range of 32 cells ... BM1:CR1

I am looking for code that will:

a) search from BM to CR looking for the address of the second instance of the word "Church"
b) return the value in the cell two cells left of it
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
I have a range of 32 cells ... BM1:CR1

I am looking for code that will:

a) search from BM to CR looking for the address of the second instance of the word "Church"
b) return the value in the cell two cells left of it
Try this:
Code:
Sub Ark68()
Const fWhat As String = "Church"
Dim R As Range, fAdr As String
With Range("BM1:CR1")
    Set R = .Find(fWhat, after:=Range(.Cells(1, .Cells.Count).Address), _
        LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, _
        searchdirection:=xlNext)
    If Not R Is Nothing Then
        fAdr = R.Address
        Set R = .FindNext(R)
        If Not R Is Nothing And R.Address <> fAdr Then
            MsgBox R.Offset(0, -2).Value
            Exit Sub
        Else
            MsgBox "A second instance of " & fWhat & " not found."
            Exit Sub
        End If
    Else
        MsgBox "Can't find " & fWhat
    End If
End With
End Sub
 
Upvote 0
One way
Code:
Sub secChoice()
Dim rng As Range, Dim c As Range, x As Long
Set rng = ActiveSheet.Range("BM1:CR1")
For Each c In rng
 If LCase(c.Value) = "church" Then
  x = x + 1
  If x = 2 Then
   MsgBox "c.Offset(0, -2).Value 'substitute cell location for msgbox
  End If
 End If
Next
End Sub
 
Upvote 0
or maybe ....

=ADDRESS(1,SMALL(IF(BM1:CR1="Church",COLUMN(BM1:CR1)),2),4)

Its an array so needs to be entered with Control + Shift + Enter

Not sure how to have it return 2 cells to left of it though...maybe one of the VBA solutions would be better
 
Upvote 0
I have a range of 32 cells ... BM1:CR1

I am looking for code that will:

a) search from BM to CR looking for the address of the second instance of the word "Church"
b) return the value in the cell two cells left of it

or maybe ....

=ADDRESS(1,SMALL(IF(BM1:CR1="Church",COLUMN(BM1:CR1)),2),4)

Its an array so needs to be entered with Control + Shift + Enter

Not sure how to have it return 2 cells to left of it though...maybe one of the VBA solutions would be better

Control+shift+enter, not just enter:
Rich (BB code):
=IFERROR(CELL("address",INDEX(BM1:CR1,SMALL(IF(BM1:CR1="church",
  COLUMN(BM1:CR1)-COLUMN(BM1)+1),2)-2)),"Not Available")
 
Upvote 0
A common formula:

Code:
=INDEX(BM1:CR1,1,MATCH("Church",BM1:CR1,0)+MATCH("Church",OFFSET(BM1:CR1,0,MATCH("Church",BM1:CR1,0)),0)-2)
 
Upvote 0
Thank you folks ... I'm still playing around with this. Thanks for all your replies! Didn't think there'd be so many ways to do it.

JoeMo ... what would change in your code if I wanted to find the 3rd instance? Or 4th?
 
Upvote 0
Thank you folks ... I'm still playing around with this. Thanks for all your replies! Didn't think there'd be so many ways to do it.

JoeMo ... what would change in your code if I wanted to find the 3rd instance? Or 4th?
To go beyond the 2nd instance, I'd restructure the code completely, like this (change the constant InstanceNumber to suit):
Code:
Sub Ark68_2()
Const fWhat As String = "Church"
Const InstanceNumber As Long = 3
Dim vA As Variant, Ct As Long
With Range("BM1:CR1")
    vA = .Value
    For i = LBound(vA, 2) To UBound(vA, 2)
        If InStr(vA(1, i), fWhat) > 0 Then
            Ct = Ct + 1
            If Ct = InstanceNumber Then
                MsgBox "Instance number " & InstanceNumber & " found in cell " & .Cells(1, i).Address
                MsgBox .Cells(1, i).Offset(0, -2).Value
                Exit Sub
            End If
        End If
    Next i
    MsgBox "Only " & Ct & " instances of " & fWhat & " were found."
End With
End Sub
 
Upvote 0
Thanks JoeMo ... I'll give it a go, but does fWhat have to be a constant. I didn't entirtely represent the full impact of what was going to happen with this code. fWhat will be variable.
 
Upvote 0
Then change:
Const fWhat as String="Church"
to:
Dim fWhat as string (or as Variant if you are looking for something other than a string)
and set fWhat in the body of the code.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top