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