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

(A7) value = JohnWinstonLennon

These Excel worksheet equations work just fine on the base Excel Worksheet. Both equations give the location for the 2nd and 3rd Capital Letters in a run-on String, such as the one above.

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. Any Help, Please!

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

CodePudding user response：

If you trying to write a function using VBA, then maybe give the following a try:

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

You could rework this into an UDF to return the position of the nth capital?