How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Question

(A7) value = JohnWinstonLennon

On the default Excel Worksheet, these equations function perfectly. In a run-on String like the one above, both equations indicate where the second and third capital letters should be placed.

I am trying to write them into a function with VBA, but I cannot figure out how to make the Indirect reference work in the function. Please, I Need Help!

=SMALL(FIND(0,SUBSTITUTE(A7,CHAR(ROW(INDIRECT("65:90"))),0)&0),2)  
=SMALL(FIND(0,SUBSTITUTE(A7,CHAR(ROW(INDIRECT("65:90"))),0)&0),3) 

Answer 1

Try this:

Sub Test()

Dim str As String: str = "JohnWinstonLennon"

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "[A-Z]"
    If .Test(str) Then
        Set matches = .Execute(str)
        For Each Match In matches
            Debug.Print Match.FirstIndex + 1
        Next
    End If
End With

End Sub