formulaArray resultat over Msgbox

[amrane]

Dear Forum


I am looking to pass the result of array formula to variable of other usage,
for instance show the result as msgbox,

is it Possible!!

Thx in advance
amrane

[Kenneth Hobs]

Notice how I used s to build a string so I could debug in a test run to see if the Immediate window showed the correct string as used in the cell manually.


Sub sample()
Dim rg_1 As Range, rg_2 As Range
Dim condit As String
Dim result As Variant
Dim s As String

condit = "total"

Set rg_1 = Sheets(1).Range(Cells(2, 4), Cells(Application.Rows.Count, 4).End(xlUp))
Set rg_2 = Sheets(1).Range(Cells(2, 5), Cells(Application.Rows.Count, 5).End(xlUp))

s = "=AVERAGE(IF(" & rg_1.Address(False, False) & "=B2," & rg_2.Address(False, False) & "))"
Debug.Print s
'=AVERAGE(IF(D2:D28=B2,E2:E28))
result = MsgBox(Evaluate(s))


MsgBox Sheet1.Range("C2")
End Sub

[amrane]

Dear Mr Kenneth Hobs

its perfect your solution, Thx, Thx a lot, you're solving big headache

amrane

[snb]

An alternatvie approach:


Sub M_snb()
sn = Sheet1.UsedRange.Columns(4).Resize(, 2)

For j = 1 To UBound(sn)
If sn(j, 1) = Cells(2, 2) Then
y = y + sn(j, 2)
x = x + 1
End If
Next

msgbox y / x
End Sub