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

[Ark68]

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

 

[JoeMo]

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:

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

 

[JLGWhiz]

One way

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

 

[Weazel]

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

 

[Aladin Akyurek]

ADVERTISEMENT

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:

=IFERROR(CELL("address",INDEX(BM1:CR1,SMALL(IF(BM1:CR1="church",
  COLUMN(BM1:CR1)-COLUMN(BM1)+1),2)-2)),"Not Available")

 

[northwolves]

A common formula:

=INDEX(BM1:CR1,1,MATCH("Church",BM1:CR1,0)+MATCH("Church",OFFSET(BM1:CR1,0,MATCH("Church",BM1:CR1,0)),0)-2)

 

[Ark68]

ADVERTISEMENT

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?

 

[JoeMo]

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):

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

 

[Ark68]

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.

 

[JoeMo]

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.