VBA - Array Formula in multiple cells

[Marcelo Branco]

Hi,

In column A i have alpha-numerics strings and i need to count the numbers in each cell. For example:

A
<TABLE style="WIDTH: 56pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=75><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=75>dfr4h67f</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>s7d34n8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>gt564xx342</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>fg453</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>34rt23</TD></TR></TBODY></TABLE>

I would like to put the results in an array using the array-formula
=COUNT(1*MID(A1,ROW($1:$30),1))

I tried

Sub TestArrayFormula()
    Dim MyRange As Range, Myarray() As Long, i As Long
 
    Set MyRange = Range("A1:A5")
 
    ReDim Myarray(1 To MyRange.Rows.Count)
 
    For i = 1 To MyRange.Rows.Count
        Myarray(i) = Evaluate("=COUNT(1*MID(A1,ROW($1:$30),1))")
        Debug.Print "Array(" & i & ") = " & Myarray(i)
    Next i
 
End Sub

No succes. It gave me, in Immediate Window, the results for A1 in all elements of the array
Array(1) = 3
Array(2) = 3
Array(3) = 3
Array(4) = 3
Array(5) = 3

I also tryed inside the For
Myarray(i) = Evaluate("=COUNT(1*MID(" & Range("A" & i) & ",ROW($1:$30),1))")

that gave me
Array(1) = 0
Array(2) = 0
Array(3) = 0
Array(4) = 0

and an error '13' when it reaches last element of the array
<TABLE style="WIDTH: 375pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=500><COLGROUP><COL style="WIDTH: 375pt; mso-width-source: userset; mso-width-alt: 18285" width=500><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: yellow; WIDTH: 375pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20 width=500>Myarray(i) = Evaluate("=COUNT(1*MID(" & Range("A" & i) & ",ROW($1:$30),1))")</TD></TR></TBODY></TABLE>

What am i missing?

Any help would be appreciated

M.

 

[Marcelo Branco]

I got it using a different approach

Sub Test2()
    Dim i As Long, j As Long, counter As Long
    Dim MyRange As Range
    Dim MyArray() As Long
 
    Set MyRange = Plan1.Range("A1:A5")
    ReDim MyArray(1 To MyRange.Rows.Count)
 
    For i = 1 To MyRange.Rows.Count
        counter = 0
        For j = 1 To Len(MyRange.Cells(i))
            If IsNumeric(Mid(MyRange.Cells(i), j, 1)) Then counter = counter + 1
        Next j
        MyArray(i) = counter
        Debug.Print "Array(" & i & ") = " & MyArray(i)
    Next i
 
End Sub

***But still puzzled why cannot use the array-formula. Any idea?***

M.