On Feb 2, 11:44*pm, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
Chechu wrote:
On Jan 31, 9:52 pm, keiji kounoike <"kounoike A | T *ma.Pikara.ne.jp"
wrote:

Keiji, THANKS a lot. I received an error 13, Type Mismatch, in the
line MsgBox "Sum of arg1 is " & Application.Sum(arg1)
The msgbox to show value on H4 works OK, shows the value (without
quotes).
Any idea where could be the issue?? I am completely missed...
Thanks!!
Cecilia

That's because the array arg1 has error values in it. You said when I
type the formula directly in Excel with the same arguments, it works OK.
so, the macro below put a formula equivalent to Sumproduct(arg1,arg2)
instead of value. then, check that formula comparing with the formula
typed directly in Excel.

Sub tracking_test2()
Const ad1 = "$aq$2:$aq$43735"
Const ad2 = "$ag$2:$ag$43735"
Const ad3 = "H4"
Dim Acwk As Workbook, Tarwk As Workbook
Dim fname As String, shname As String
Dim arg1, arg2

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set Acwk = ActiveWorkbook

If Not Application.Dialogs(xlDialogOpen).Show Then
* * * Exit Sub
End If

Set Tarwk = ActiveWorkbook
fname = Tarwk.Name
shname = Tarwk.Worksheets(1).Name
Acwk.Activate
Worksheets("tracking").Select

'arg1 = Application.Evaluate("--([" & fname & "]" & shname & "!" & _
* * * * * ad1 & " = " & ad3 & ")")

'arg2 = Application.Evaluate("--([" & fname & "]" & shname & "!" & _
* * * * * ad2 & ")")

'Range("al12") = Application.SumProduct(arg1, arg2)

Range("al12").Formula = _
* * *"=SumProduct(" & "--([" & fname & "]" & shname & "!" & ad1 & _
* * *" = " & ad3 & "), --([" & fname & "]" & shname & "!" & ad2 & "))"

'Tarwk.Close

End Sub

Keiji



Keiji, thanks for your help on this. The error that I get now it #
1004, application-defined or object-defined error in the line:
Range("al12").Formula = _
"=SumProduct(" & "--([" & fname & "]" & shname & "!" & ad1 & _
" = " & ad3 & "), --([" & fname & "]" & shname & "!" & ad2 &
"))"

I tried with application.evaluate, and still get #value eror!! This is
very strange....
Thanks,
Cecilia