Chechu wrote:
On Jan 31, 2:55 am, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
I'm not sure if this works or not, but try this one. I don't know your
worksheet's name that has data, so I presume your data always reside in
the first worksheet from left in the selected file.

Keiji and Don, thank you very much for your time. Keiji, I think that
your macro is very close to the final, and the assumption on first
worksheet is perfect. But I am receiving #VALUE! error as result. I
tested each variable with msgbox and they all look OK, except ad3. The
ad3, or cell H4, is a text, and may be that's causing the issue. Then
I changed in your code:
Const ad3 = "H4" by Dim ad3 As String
and added:
ad3 = Range("H4").Text
If Not IsNumeric(ad3) Then
ad3 = """" & ad3 & """"
End If

Entire code is below. Any ideas on why it is still not working??? I am
pretty sure it's just a matter of syntax.... Can't figure out where.
Needless to say it, when I type the formula directly in Excel with the
same arguments, it works OK.

Thanks again,
Cecilia


I don't have your data. So, I couldn't find out the causes of error.
But, I don't think ad3 is the issue. Evaluate(ad3), that means
Evaluate("H4"), returns Range("H4") in ActiveWorksheet, and default is
the value of the that range.

Try the code below, and it would stop by error where something wrong.

Sub tracking_test1()
Const ad1 = "$aq$2:$aq$43735"
Const ad2 = "$ag$2:$ag$43735"
Const ad3 = "H4"
'Dim ad3 As String
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

'ad3 = Range("H4").Text

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

'If Not IsNumeric(ad3) Then
' ad3 = """" & ad3 & """"
'End If

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

MsgBox "Range(H4)'s Value is " & Application.Evaluate(ad3) '<<==Add

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

MsgBox "Sum of arg1 is " & Application.Sum(arg1) '<<==Add

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

MsgBox "Sum of arg2 is " & Application.Sum(arg2) '<<==Add

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

Tarwk.Close
End Sub

Keiji