SumProduct in VBA - Allow the user to select the file

C

Chechu

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").Select
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
 
D

Don Guillett

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 tab>view code>insert 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
(e-mail address removed)
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").Select
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
 
C

Chechu

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
 
K

keiji kounoike

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
 
D

Don Guillett

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
(e-mail address removed)
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



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 tab>view code>insert 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

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").Select
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
Click to expand...
 
C

Chechu

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


I have the following macro:
Click to expand...
Sub tracking()
Click to expand...
engid = "'[Export January.xls]Export_January'!$aq$2:$aq$43735"
rev = "'[Export January.xls]Export_January'!$ag$2:$ag$43735"
Click to expand...
engid2 = Sheets("actual").Range("H4").Value
Click to expand...
If Not IsNumeric(engid2) Then
  engid2 = """" & engid2 & """"
End If
pct = Application.Evaluate("SUMPRODUCT(--(" & engid & "=" _
 & engid2 & "),--(" & rev & "))")
Click to expand...
ThisWorkbook.Worksheets("actual").Range("AL12").Select
Range("al12").Value = pct
Click to expand...
End Sub
Click to expand...
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:
Click to expand...
Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
If .Show = -1 Then
Set sourcebook = Workbooks.Open(Filename:=fd.SelectedItems(1))
    With sourcebook….???????
Click to expand...
……..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???
Click to expand...
Thanks,
Cecilia- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
K

keiji kounoike

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
Click to expand...

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
 
C

Chechu

Chechu said:
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
Click to expand...
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.
Click to expand...
Thanks again,
Cecilia
Click to expand...

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 -
Click to expand...

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
 
K

keiji kounoike

Chechu said:
On Jan 31, 9:52 pm, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp">
wrote:
Click to expand...
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
Click to expand...

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
 
C

Chechu

Chechu said:
On Jan 31, 9:52 pm, keiji kounoike <"kounoike A | T  ma.Pikara.ne.jp">
wrote:
Click to expand...
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
Click to expand...

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
Click to expand...



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
 
K

keiji kounoike

Chechu said:
On Feb 2, 11:44 pm, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp">
wrote:
Click to expand...
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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to Reset ButtonName and Filters in msoFileDialogOpen? 1
Novice q: allow user to select worksheet in middle of macro 3
VBA code help for linking Word file to Excel file 7
Help in Getting the File Name from a path 3
Allow user input into VBA Module? 4
Excel macro range problem 3
Allow user select file to copy data from "Application.GetOpenFilen 3
Clean up code and port to Mac 0

Top