Re: SumProduct in VBA - Allow the user to select the file
- From: keiji kounoike <"kounoike A | T ma.Pikara.ne.jp">
- Date: Wed, 03 Feb 2010 11:44:10 +0900
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
.
- Follow-Ups:
- Prev by Date: Paste Value Toolbar Icon
- Next by Date: RE: Run Time Error on trying to save workbook
- Previous by thread: Paste Value Toolbar Icon
- Next by thread: Re: SumProduct in VBA - Allow the user to select the file
- Index(es):
Relevant Pages
|
