I have the following macro:

Sub tracking()

engid = "'[Export January.xls]Export_January'!$aq$2:$aq$43735"
rev = "'[Export January.xls]Export_January'!$ag$2:$ag$43735"

engid2 = Sheets("actual").Range("H4").Value

If Not IsNumeric(engid2) Then
engid2 = """" & engid2 & """"
End If
pct = Application.Evaluate("SUMPRODUCT(--(" & engid & "=" _
& engid2 & "),--(" & rev & "))")

ThisWorkbook.Worksheets("actual").Range("AL12").Se lect
Range("al12").Value = pct

End Sub

It works OK but, source file is hard coded. What I want to do is to
allow the user to select the source file, and probably open the file
(sumproduct in VBA works with closed files??? I don’t think so). I
know I should include something like:

Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
..AllowMultiSelect = False
If .Show = -1 Then
Set sourcebook = Workbooks.Open(Filename:=fd.SelectedItems(1))
With sourcebook….???????

……..But how to assign to a variable the file name for the selected
file, and also, how to modify the sumproduct syntax to use it???

Thanks,
Cecilia

Sumproduct can work with closed files but indirect doesn't so
How about this idea of a drop down list with the file names and
worksheet_change macro
right click sheet tabview codeinsert this. now when 2010 is selected from
the drop down list the 4 digit year code is replaced with 2010

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < Range("j16").Address Then Exit Sub
Set mr = Range("j15")
fxls = InStr(mr.Formula, ".xls")
'MsgBox Mid(mr.Formula, fxls - 4, 4)
mr.Replace Mid(mr.Formula, fxls - 4, 4), Target
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chechu" wrote in message
...
I have the following macro:

Sub tracking()

engid = "'[Export January.xls]Export_January'!$aq$2:$aq$43735"
rev = "'[Export January.xls]Export_January'!$ag$2:$ag$43735"

engid2 = Sheets("actual").Range("H4").Value

If Not IsNumeric(engid2) Then
engid2 = """" & engid2 & """"
End If
pct = Application.Evaluate("SUMPRODUCT(--(" & engid & "=" _
& engid2 & "),--(" & rev & "))")

ThisWorkbook.Worksheets("actual").Range("AL12").Se lect
Range("al12").Value = pct

End Sub

It works OK but, source file is hard coded. What I want to do is to
allow the user to select the source file, and probably open the file
(sumproduct in VBA works with closed files??? I don�t think so). I
know I should include something like:

Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
..AllowMultiSelect = False
If .Show = -1 Then
Set sourcebook = Workbooks.Open(Filename:=fd.SelectedItems(1))
With sourcebook�.???????

��..But how to assign to a variable the file name for the selected
file, and also, how to modify the sumproduct syntax to use it???

Thanks,
Cecilia

Don,
Thanks a lot for your answer. I am a little confused, probably
misunderstanding your idea. I need the user to browse on its computer
and select the file to be used by the sumproduct, thus I can’t have a
list in the spreadsheet. So if I am able to store the name of the
selected file in Application.FileDialog(msoFileDialogOpen), store the
name of the first worksheet in the selected file, and pass those names
into the sumproduct, shouldn’t work??
Thanks a lot,
Cecilia



On Jan 30, 1:47*pm, "Don Guillett" wrote:
Sumproduct can work with closed files but indirect doesn't so
How about this idea of a drop down list with the file names and
worksheet_change macro
right click sheet tabview codeinsert this. now when 2010 is selected from
the drop down list the 4 digit year code is replaced with 2010

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < Range("j16").Address Then Exit Sub
Set mr = Range("j15")
fxls = InStr(mr.Formula, ".xls")
* 'MsgBox Mid(mr.Formula, fxls - 4, 4)
mr.Replace Mid(mr.Formula, fxls - 4, 4), Target
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Chechu" wrote in message

...
I have the following macro:

Sub tracking()

engid = "'[Export January.xls]Export_January'!$aq$2:$aq$43735"
rev = "'[Export January.xls]Export_January'!$ag$2:$ag$43735"

engid2 = Sheets("actual").Range("H4").Value

If Not IsNumeric(engid2) Then
* engid2 = """" & engid2 & """"
End If
pct = Application.Evaluate("SUMPRODUCT(--(" & engid & "=" _
*& engid2 & "),--(" & rev & "))")

ThisWorkbook.Worksheets("actual").Range("AL12").Se lect
Range("al12").Value = pct

End Sub

It works OK but, source file is hard coded. What I want to do is to
allow the user to select the source file, and probably open the file
(sumproduct in VBA works with closed files??? I don t think so). I
know I should include something like:

Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
If .Show = -1 Then
Set sourcebook = Workbooks.Open(Filename:=fd.SelectedItems(1))
* * With sourcebook .???????

..But how to assign to a variable the file name for the selected
file, and also, how to modify the sumproduct syntax to use it???

Thanks,
Cecilia

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.

Sub tracking_test()
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("actual").Select
arg1 = Application.Evaluate("--([" & fname & "]" & shname & "!" & _
ad1 & " = " & ad3 & ")")
arg2 = Application.Evaluate("--([" & fname & "]" & shname & "!" & _
ad2 & ")")
Range("al12") = Application.SumProduct(arg1, arg2)
Tarwk.Close
End Sub

Keiji

Chechu wrote:
I have the following macro:

Sub tracking()

engid = "'[Export January.xls]Export_January'!$aq$2:$aq$43735"
rev = "'[Export January.xls]Export_January'!$ag$2:$ag$43735"

engid2 = Sheets("actual").Range("H4").Value

If Not IsNumeric(engid2) Then
engid2 = """" & engid2 & """"
End If
pct = Application.Evaluate("SUMPRODUCT(--(" & engid & "=" _
& engid2 & "),--(" & rev & "))")

ThisWorkbook.Worksheets("actual").Range("AL12").Se lect
Range("al12").Value = pct

End Sub

It works OK but, source file is hard coded. What I want to do is to
allow the user to select the source file, and probably open the file
(sumproduct in VBA works with closed files??? I don’t think so). I
know I should include something like:

Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
If .Show = -1 Then
Set sourcebook = Workbooks.Open(Filename:=fd.SelectedItems(1))
With sourcebook….???????

……..But how to assign to a variable the file name for the selected
file, and also, how to modify the sumproduct syntax to use it???

Thanks,
Cecilia

You can use this to get the filename.Try it and insert that into
range("j17").formula="=yourformula"

Sub FileBrowseSAS()
fn = Application.GetOpenFilename
If fn = "False" Then Exit Sub
MsgBox fn
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chechu" wrote in message
...
Don,
Thanks a lot for your answer. I am a little confused, probably
misunderstanding your idea. I need the user to browse on its computer
and select the file to be used by the sumproduct, thus I can�t have a
list in the spreadsheet. So if I am able to store the name of the
selected file in Application.FileDialog(msoFileDialogOpen), store the
name of the first worksheet in the selected file, and pass those names
into the sumproduct, shouldn�t work??
Thanks a lot,
Cecilia



On Jan 30, 1:47�pm, "Don Guillett" wrote:
Sumproduct can work with closed files but indirect doesn't so
How about this idea of a drop down list with the file names and
worksheet_change macro
right click sheet tabview codeinsert this. now when 2010 is selected
from
the drop down list the 4 digit year code is replaced with 2010

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < Range("j16").Address Then Exit Sub
Set mr = Range("j15")
fxls = InStr(mr.Formula, ".xls")
� 'MsgBox Mid(mr.Formula, fxls - 4, 4)
mr.Replace Mid(mr.Formula, fxls - 4, 4), Target
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Chechu" wrote in message

...
I have the following macro:

Sub tracking()

engid = "'[Export January.xls]Export_January'!$aq$2:$aq$43735"
rev = "'[Export January.xls]Export_January'!$ag$2:$ag$43735"

engid2 = Sheets("actual").Range("H4").Value

If Not IsNumeric(engid2) Then
� engid2 = """" & engid2 & """"
End If
pct = Application.Evaluate("SUMPRODUCT(--(" & engid & "=" _
�& engid2 & "),--(" & rev & "))")

ThisWorkbook.Worksheets("actual").Range("AL12").Se lect
Range("al12").Value = pct

End Sub

It works OK but, source file is hard coded. What I want to do is to
allow the user to select the source file, and probably open the file
(sumproduct in VBA works with closed files??? I don t think so). I
know I should include something like:

Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
If .Show = -1 Then
Set sourcebook = Workbooks.Open(Filename:=fd.SelectedItems(1))
� � With sourcebook .???????

..But how to assign to a variable the file name for the selected
file, and also, how to modify the sumproduct syntax to use it???

Thanks,
Cecilia

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.

Sub tracking_test()
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("actual").Select
arg1 = Application.Evaluate("--([" & fname & "]" & shname & "!" & _
* * * * *ad1 & " = " & ad3 & ")")
arg2 = Application.Evaluate("--([" & fname & "]" & shname & "!" & _
* * * * *ad2 & ")")
Range("al12") = Application.SumProduct(arg1, arg2)
Tarwk.Close
End Sub

Keiji



Chechu wrote:
I have the following macro:

Sub tracking()

engid = "'[Export January.xls]Export_January'!$aq$2:$aq$43735"
rev = "'[Export January.xls]Export_January'!$ag$2:$ag$43735"

engid2 = Sheets("actual").Range("H4").Value

If Not IsNumeric(engid2) Then
* engid2 = """" & engid2 & """"
End If
pct = Application.Evaluate("SUMPRODUCT(--(" & engid & "=" _
*& engid2 & "),--(" & rev & "))")

ThisWorkbook.Worksheets("actual").Range("AL12").Se lect
Range("al12").Value = pct

End Sub

It works OK but, source file is hard coded. What I want to do is to
allow the user to select the source file, and probably open the file
(sumproduct in VBA works with closed files??? I don’t think so). I
know I should include something like:

Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
If .Show = -1 Then
Set sourcebook = Workbooks.Open(Filename:=fd.SelectedItems(1))
* * With sourcebook….???????

……..But how to assign to a variable the file name for the selected
file, and also, how to modify the sumproduct syntax to use it???

Thanks,
Cecilia- Hide quoted text -

- Show quoted text -

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


Sub tracking_test()
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
arg1 = Application.Evaluate("--([" & fname & "]" & shname & "!" & _
ad1 & " = " & ad3 & ")")
arg2 = Application.Evaluate("--([" & fname & "]" & shname & "!" & _
ad2 & ")")
Range("al12") = Application.SumProduct(arg1, arg2)

Tarwk.Close
End Sub

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

On Jan 31, 9:52*pm, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
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- Hide quoted text -

- Show quoted text -

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

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

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

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


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

Hi Chechu

I can't reproduce that error, and I have only two things to ask you.
However, I don't think this would nail down the problem. To tell the
truth, I almost give up.

first, put the code below

MsgBox "=SumProduct(" & "--([" & fname & "]" & shname & "!" & ad1 & _
" = " & ad3 & "), --([" & fname & "]" & shname & "!" & ad2 & "))"

before Range("al12").Formula = _ ... and check the formula. Second, put
a simple code like

Range("al12").Formula = "=A1"

, and check this will work or not.

If you don't mind, send your code and data. then i will check it.
My address is kounoike at ma.Pikara.ne.jp

Regards
Keiji