Unable to record complicated formula in macro

[[email protected]]

I have a formula that requires to be entered as an array by
CTRL+SHIFT+ENTER in order to execute correctly. I tried to record a
macro with this formula, it says "unable to record macro". The
following is an example of my formula:

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

which can also be found here
http://office.microsoft.com/en-us/as...549011033.aspx


Any ideas around this?

[Leith Ross]

Hello Viduo,

You can use a User Defined Function (UDF) in place of the Worksheet functions. Add a VBA module to your Workbook's Project and copy this code into it.

Example of Uing the UDF:
A1 = "abc123efg"
B1 = "=GetNumbers(A1)"
B1 will contain 123

Public Function GetNumbers(ByRef Cell As Range)

Application.Volatile

Dim I As Long
Dim N As Long
Dim X As String

For I = 1 To Len(Cell.Text)
N = Asc(Mid(Cell.Text, I, 1))
If N > 47 And N < 58 Then X = X & Chr$(N)
Next I

GetNumbers = X

End Function

Sincerely,
Leith Ross