SumProduct in VBA - Allow the user to select the file

I have the following macro:

Sub tracking()

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

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

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

ThisWorkbook.Worksheets("actual").Range("AL12").Select
Range("al12").Value =3D 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=92t think so). I
know I should include something like:

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

=85=85..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
0
Chechu
1/30/2010 3:26:36 PM
excel.programming 6508 articles. 2 followers. Follow

10 Replies
1502 Views

Similar Articles

[PageSpeed] 11

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
dguillett@gmail.com
"Chechu" <cecichi9@hotmail.com> wrote in message 
news:656fa3a6-32db-472b-a5a7-7219a68a6caf@m16g2000yqc.googlegroups.com...
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 

0
Don
1/30/2010 4:47:01 PM
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=92t 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=92t work??
Thanks a lot,
Cecilia



On Jan 30, 1:47=A0pm, "Don Guillett" <dguille...@gmail.com> 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 tab>view code>insert this. now when 2010 is selected fr=
om
> 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 =3D Range("j15")
> fxls =3D InStr(mr.Formula, ".xls")
> =A0 '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
> dguill...@gmail.com"Chechu" <cecic...@hotmail.com> wrote in message
>
> news:656fa3a6-32db-472b-a5a7-7219a68a6caf@m16g2000yqc.googlegroups.com...
> I have the following macro:
>
> Sub tracking()
>
> engid =3D "'[Export January.xls]Export_January'!$aq$2:$aq$43735"
> rev =3D "'[Export January.xls]Export_January'!$ag$2:$ag$43735"
>
> engid2 =3D Sheets("actual").Range("H4").Value
>
> If Not IsNumeric(engid2) Then
> =A0 engid2 =3D """" & engid2 & """"
> End If
> pct =3D Application.Evaluate("SUMPRODUCT(--(" & engid & "=3D" _
> =A0& engid2 & "),--(" & rev & "))")
>
> ThisWorkbook.Worksheets("actual").Range("AL12").Select
> Range("al12").Value =3D 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 =3D Application.FileDialog(msoFileDialogOpen)
> With fd
> .AllowMultiSelect =3D False
> If .Show =3D -1 Then
> Set sourcebook =3D Workbooks.Open(Filename:=3Dfd.SelectedItems(1))
> =A0 =A0 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

0
Chechu
1/31/2010 1:47:11 AM
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").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
0
keiji
1/31/2010 5:55:57 AM
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
dguillett@gmail.com
"Chechu" <cecichi9@hotmail.com> wrote in message 
news:a1714a3e-37e0-4336-ae9f-53a6c82b23e8@m25g2000yqc.googlegroups.com...
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" <dguille...@gmail.com> 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 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
> dguill...@gmail.com"Chechu" <cecic...@hotmail.com> wrote in message
>
> news:656fa3a6-32db-472b-a5a7-7219a68a6caf@m16g2000yqc.googlegroups.com...
> 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

0
Don
1/31/2010 1:42:13 PM
On Jan 31, 2:55=A0am, 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 =3D "$aq$2:$aq$43735"
> Const ad2 =3D "$ag$2:$ag$43735"
> Const ad3 =3D "H4"
> Dim Acwk As Workbook, Tarwk As Workbook
> Dim fname As String, shname As String
> Dim arg1, arg2
>
> Application.ScreenUpdating =3D False
> Application.DisplayAlerts =3D False
> Set Acwk =3D ActiveWorkbook
>
> If Not Application.Dialogs(xlDialogOpen).Show Then
> =A0 =A0 =A0Exit Sub
> End If
>
> Set Tarwk =3D ActiveWorkbook
> fname =3D Tarwk.Name
> shname =3D Tarwk.Worksheets(1).Name
> Acwk.Activate
> Worksheets("actual").Select
> arg1 =3D Application.Evaluate("--([" & fname & "]" & shname & "!" & _
> =A0 =A0 =A0 =A0 =A0ad1 & " =3D " & ad3 & ")")
> arg2 =3D Application.Evaluate("--([" & fname & "]" & shname & "!" & _
> =A0 =A0 =A0 =A0 =A0ad2 & ")")
> Range("al12") =3D Application.SumProduct(arg1, arg2)
> Tarwk.Close
> End Sub
>
> Keiji
>
>
>
> Chechu wrote:
> > I have the following macro:
>
> > Sub tracking()
>
> > engid =3D "'[Export January.xls]Export_January'!$aq$2:$aq$43735"
> > rev =3D "'[Export January.xls]Export_January'!$ag$2:$ag$43735"
>
> > engid2 =3D Sheets("actual").Range("H4").Value
>
> > If Not IsNumeric(engid2) Then
> > =A0 engid2 =3D """" & engid2 & """"
> > End If
> > pct =3D Application.Evaluate("SUMPRODUCT(--(" & engid & "=3D" _
> > =A0& engid2 & "),--(" & rev & "))")
>
> > ThisWorkbook.Worksheets("actual").Range("AL12").Select
> > Range("al12").Value =3D 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=92t think so). I
> > know I should include something like:
>
> > Set fd =3D Application.FileDialog(msoFileDialogOpen)
> > With fd
> > .AllowMultiSelect =3D False
> > If .Show =3D -1 Then
> > Set sourcebook =3D Workbooks.Open(Filename:=3Dfd.SelectedItems(1))
> > =A0 =A0 With sourcebook=85.???????
>
> > =85=85..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 =3D "H4" by Dim ad3 As String
and added:
 ad3 =3D Range("H4").Text
If Not IsNumeric(ad3) Then
  ad3 =3D """" & 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 =3D "$aq$2:$aq$43735"
Const ad2 =3D "$ag$2:$ag$43735"
'Const ad3 =3D "H4"
Dim ad3 As String
Dim Acwk As Workbook, Tarwk As Workbook
Dim fname As String, shname As String
Dim arg1, arg2


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

ad3 =3D Range("H4").Text

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

If Not IsNumeric(ad3) Then
  ad3 =3D """" & ad3 & """"
End If

Set Tarwk =3D ActiveWorkbook
fname =3D Tarwk.Name
shname =3D Tarwk.Worksheets(1).Name
Acwk.Activate
Worksheets("tracking").Select
arg1 =3D Application.Evaluate("--([" & fname & "]" & shname & "!" & _
         ad1 & " =3D " & ad3 & ")")
arg2 =3D Application.Evaluate("--([" & fname & "]" & shname & "!" & _
         ad2 & ")")
Range("al12") =3D Application.SumProduct(arg1, arg2)

Tarwk.Close
End Sub





0
Chechu
1/31/2010 4:24:08 PM
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
0
keiji
2/1/2010 12:52:08 AM
On Jan 31, 9:52=A0pm, keiji kounoike <"kounoike A | T  ma.Pikara.ne.jp">
wrote:
> Chechu wrote:
> > On Jan 31, 2:55 am, keiji kounoike <"kounoike A | T =A0ma.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 i=
n
> >> 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 =3D "H4" by Dim ad3 As String
> > and added:
> > =A0ad3 =3D Range("H4").Text
> > If Not IsNumeric(ad3) Then
> > =A0 ad3 =3D """" & 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 =3D "$aq$2:$aq$43735"
> Const ad2 =3D "$ag$2:$ag$43735"
> Const ad3 =3D "H4"
> 'Dim ad3 As String
> Dim Acwk As Workbook, Tarwk As Workbook
> Dim fname As String, shname As String
> Dim arg1, arg2
>
> Application.ScreenUpdating =3D False
> Application.DisplayAlerts =3D False
> Set Acwk =3D ActiveWorkbook
>
> 'ad3 =3D Range("H4").Text
>
> If Not Application.Dialogs(xlDialogOpen).Show Then
> =A0 =A0 =A0 Exit Sub
> End If
>
> 'If Not IsNumeric(ad3) Then
> ' =A0ad3 =3D """" & ad3 & """"
> 'End If
>
> Set Tarwk =3D ActiveWorkbook
> fname =3D Tarwk.Name
> shname =3D Tarwk.Worksheets(1).Name
> Acwk.Activate
> Worksheets("tracking").Select
>
> MsgBox "Range(H4)'s Value is " & Application.Evaluate(ad3) '<<=3D=3DAdd
>
> arg1 =3D Application.Evaluate("--([" & fname & "]" & shname & "!" & _
> =A0 =A0 =A0 =A0 =A0 ad1 & " =3D " & ad3 & ")")
>
> MsgBox "Sum of arg1 is " & Application.Sum(arg1) '<<=3D=3DAdd
>
> arg2 =3D Application.Evaluate("--([" & fname & "]" & shname & "!" & _
> =A0 =A0 =A0 =A0 =A0 ad2 & ")")
>
> MsgBox "Sum of arg2 is " & Application.Sum(arg2) '<<=3D=3DAdd
>
> Range("al12") =3D 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
0
Chechu
2/2/2010 2:45:07 PM
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
0
keiji
2/3/2010 2:44:10 AM
On Feb 2, 11:44=A0pm, keiji kounoike <"kounoike A | T  ma.Pikara.ne.jp">
wrote:
> Chechu wrote:
> > On Jan 31, 9:52 pm, keiji kounoike <"kounoike A | T =A0ma.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 =3D "$aq$2:$aq$43735"
> Const ad2 =3D "$ag$2:$ag$43735"
> Const ad3 =3D "H4"
> Dim Acwk As Workbook, Tarwk As Workbook
> Dim fname As String, shname As String
> Dim arg1, arg2
>
> Application.ScreenUpdating =3D False
> Application.DisplayAlerts =3D False
> Set Acwk =3D ActiveWorkbook
>
> If Not Application.Dialogs(xlDialogOpen).Show Then
> =A0 =A0 =A0 Exit Sub
> End If
>
> Set Tarwk =3D ActiveWorkbook
> fname =3D Tarwk.Name
> shname =3D Tarwk.Worksheets(1).Name
> Acwk.Activate
> Worksheets("tracking").Select
>
> 'arg1 =3D Application.Evaluate("--([" & fname & "]" & shname & "!" & _
> =A0 =A0 =A0 =A0 =A0 ad1 & " =3D " & ad3 & ")")
>
> 'arg2 =3D Application.Evaluate("--([" & fname & "]" & shname & "!" & _
> =A0 =A0 =A0 =A0 =A0 ad2 & ")")
>
> 'Range("al12") =3D Application.SumProduct(arg1, arg2)
>
> Range("al12").Formula =3D _
> =A0 =A0 =A0"=3DSumProduct(" & "--([" & fname & "]" & shname & "!" & ad1 &=
 _
> =A0 =A0 =A0" =3D " & 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 =3D _
     "=3DSumProduct(" & "--([" & fname & "]" & shname & "!" & ad1 & _
     " =3D " & ad3 & "), --([" & fname & "]" & shname & "!" & ad2 &
"))"

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



0
Chechu
2/3/2010 3:25:20 PM
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
0
keiji
2/4/2010 2:57:48 AM
Reply:

Similar Artilces:

Roles and 'Settings' for user interface CRM 3.0
Hi, I'm wanting to create a role for a new user that has basic access. His web interface allows him access to the Settings Tab, which I don't want. From there he can change 'Business Unit Settings' and other settings. I thought it was a matter of my selecting the wrong settings in the role creation, so I created a 'Basic' role with NOTHING checked off and assigned it to him exclusively. It still gave him access to the settings tab, and 'Business Unit Settings'. Surely there must be a way to limit user access to this tab. Any ideas? Hi Jonathan, Have you ...

macro embedded in an xml file
Hi, just wondering if anyone knows if you can embedd a macro in an xml file? I have an xml file that I create with a .net web application and it opens with Excel, but would be nice to attatch a macro to it as well. Thanks. -- Paul G Software engineer. Paul wrote: > Hi, just wondering if anyone knows if you can embedd a macro in an > xml file? XML isn't a programming language, so it can't have macros in the sense that you mean them. See http://xml.silmaril.ie/authors/execute/ Some processors (including browsers) may react to special features like Processing Instructions to...

Sumproduct
Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

Best way to separate user accounts?
We are running Exchange 2003 Enterprise Edition in single server, single site mode., with AV and antispyware running locally on the server as well. There are about 500 accounts for faculty and staff and the server runs fine with little performance issues. The server is a Dell PowerEdge 2850, with a dual Xeon 2.8 Ghz processor, 4 GB memory, 300 GB disk space, with 250 GB being free. Just today it was requested that we create Exchange accounts for ~200 graduate students on this server. Given the information above I am interested in what the experts on this list think would be the best r...

VBA Text Color
I would like to change the text color to Red for a string being returned to a window through vba when certain conditions are met. I can see the vbRed control but cannot get it to work, Object Required. Has anyone been able to change the text color through vba, and how is it set? Mick Hi Mick I don't believe VBA can directly change the colours on a Dexterity field or window. You can use the unsupported but very useful method of pass through Dexterity SanScript to change the colour of the text or the background. I think you already have the company background colour VBA example. I...

cannot reassign record to other user
Dear all, 5 person leave our company. I want to disable their user but before that i need to reassign their records first. The first attempt works fine. But the next 3 account raise error message everytime i run reassign records. So there are only 2 account from 5 account which can run reassign record successfully. Do you know what it might be wrong? PS. Here are the pop up error message: "An error has occured. Try this action again. If the problem continues, check the Microsoft Dynamics CRM Community for solutions or contact your organization's Microsot Dynamics CRM Adminis...

Highlighting the selected row or column
Is there a way to put the cursor in a cell and have that entire row and/or column highlighted? I would like the highlighting to be temporary and move with the selected cell. Thank you in advance. Lewis, have a look at Chip's addin at http://www.cpearson.com/excel/rowliner.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Lewis Clark" <lewis_clark_644 @ yahoo.com> wrote in message news:HwLce.637$nN.574@trn...

Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

Startup script to delete file
Hello, I'm afraid I'm not well versed in Windows Server, but I think what I want to do is very simple. I need to have the startup of Server 2003 delete a file that may or may not exist. And here is why - when the server is not shutdown correctly (e.g. power outage), the .pid file for our rsync service does not get deleted. So when the server starts up again, the rsync service does not start because the .pid file exists. So I'd like to have the file deleted on startup, just in case it is there. I've done a lot of research, but am getting very confuse...

How to open previous version of Publisher file when I only have 20
Keep getting, "Publisher cannot open files from a different version". I thooght there was some sort of converter or patch to do this. Different version does not mean previous. Usually it means newer. There is no patch or converter for this. You can ask the originator to save it as your version or send it as a .pdf. JoAnn Paules MVP Publisher "walko789" wrote: > Keep getting, "Publisher cannot open files from a different version". I > thooght there was some sort of converter or patch to do this. ...

Conditional Formatting with XML
I recently became aware of the possibility of using conditional formatting to highlight the cells in a spreadsheet which contain formulas. This uses the GET.CELL method from XML. While it cannot reference another workbook (like Personal.xls), I have still been able to write a macro in my Personal.xls folder that sets the name range and conditional references in the active workbook; while I "wrote" it, I heavily borrowed from others posts on a website :) The function and sub below toggle this conditional formatting for the entire worksheet. Question: how do I set this up so that ...

VBA Excel: Move rows which match a certain feature between tabs
Hi, I have a hughe matrix of numbers. Such matrix has headers in its columns (first row). Say that headers are A, B and C (). What I want to do with a VBA script is to look into the rows to check if what of them have the value "30" in the column header. In such case, I would like to move the row from that tab to another one called, e.g., "Tab_30". An important point is that I need that the row in the original matrix has to be deleted. I do not like blank rows in the original matrix nor the new matrix in the tab "Tab_30". I am not an expert in Excel. I just rec...

.pst file size limitation
I read somewhere that there is a file limitation for .pst at 2GB.. beyond 2GB in size, the whole file is subject to problems an issues... I receive a huge amount of emails not to mention attachments in larg sizes... I reach the 2GB limit within 6months... Can someone please provide some solution on how I can overcome the 2G limitation -- obeni ----------------------------------------------------------------------- obenix's Profile: http://www.msusenet.com/member.php?userid=401 View this thread: http://www.msusenet.com/t-187096263 Dear, Yes, there is 2GB(in exact 1.82GB) PST file siz...

Unpack Files to memory and then run them (exe protector style)
Is it possible in anyway to load a file into memory and then run it from there? I am working on a file compressor (www.nemokprod.go.ro/nb.htm) that can compress and encrypt and save multiple files as an exe file that can then run the compressed files after unpacking them to a temp folder. The problem is that I have to unpack the files to the hard-disk and then run them from there, making them vulnerable to user that may try to get the original (unprotected) files. So the user shouldn't have access to the file operations in the background. So I need to keep the original unpacked files hidd...

If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16) *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0) It works great....except... In column $M$3:$M$189 there are also blank cells and I want to count these a...

SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]Requirements'!$F$2:$F$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]R equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...

adding user control to a cell in a DataGridView
Instead of one of the existing column types I would like to have a user control appear in a cell in a gridview. What I have done is created a control CCTest : Control, IDataGridViewEditingControl inherits from Control, and implements IDataGridViewEditingControl I then create a cell class TestCell : DataGridViewCell in here I override public override void InitializeEditingControl(int rowIndex, object initialFormattedValue, DataGridViewCellStyle dataGridViewCellStyle) to set the editing control for the cell. these get used in a column I create and add to t...

Exchange admin viewing users emails
I am the Exchange admin for my company and I need to view the emails of one of the employees and am unable to do so. My account is already part of the domain admins and the permissions have been added on the Exchange System Manager, but when I try to open the Inbox in my Outlook I get an error. (File -> Open -> Other User's Folder). When I get the name and select Inbox I receive an error "Unable to display the folder. The information store could not be opened." I have even specifically added my own account with all permissions and received the same error. Please h...

VBA
Hi, I am currently writing a database and what i want it to do is when one particular field is filled in eg. Cylinder, then it will pick up only "cylinder" items. I have tried this code below but it doesnt seem to work. Could someone please help me. Private Sub Job_Installable_AfterUpdate() 'If Job_Installable.[Column](1) = "Cylinder" Then ' 'Forms!OrderForm.ctlSubForm.Form!Controls.NewData ' 'Forms! ' [tblJob_Consumables SubForm]![Job_Consumable Consumable ID].ControlSource = "qlkpConsumableCylinder" 'Else...

Problem opening files from server
When attempting to open a file from the server by clicking the short cut to the folder on the server, then the file I get the following message: Windows cannot find 'L:\name_of-file.xls'. Make sure you typed the name correctly, and then try again. If I bring up excel 2007, then click the "folder" to the right of the 'Office Button' (upper left hand corner), it brings up a listing of the network drives and the files under them. I can click on the *.xls that I desire to open and it opens it without a problem. Also, I can click the 'Office Button' and th...

Help Debug Complex Formula (SUMPRODUCT? SUMIF?)
Folks, I'm really struggling with this one. I've got 354 rows of data (rows 3:352). In column G there may be a date. In column M there is a number (1-12) which represents a monthly period, and in column O there is a dollar amount which represents a montly invoice total. I need to construct a formula which calculates the sum of O for a specific period M where there is a date entered (non-blank cell) in G. The formula below is what I constructed but it does not work. Rather it calculates the sum of O for the specified period in M but then multiplies the sum ($16,200) by the num...

Storing files in folders
Hi, I have Outlook 2002. How do I store all files in a folder to a CD-Rom disk. I have tons of video clips jokes that people send me. Thanks in advance. David David <dy168@cox.net> wrote: > I have Outlook 2002. How do I store all files in a folder to a > CD-Rom disk. I have tons of video clips jokes that people send me. The simplest say is to close Outlook and copy your PST to a CD. To be more selective, create a new PST and move the folder you wish to preserve to that PST. Close Outlook and copy that PST to the CD. -- Brian Tillman ...

How to merge 10 files in one step?
Hi Everyone I would like to check is it possible to merge 10 files with the same format together with one step or less steps when compared to opening up 10 files and then copy and paste? I would really appreciate any help given... Cheers ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements There is no way built into Excel to do a merge other than copy/paste. (Well, there is th...

Center a string VBA
I have created this excel VBA function to center a string. It works but is there a better way? Sub Test() MsgBox CenterString("Center This String", 50) End Sub Function CenterString(xInput As String, xLength As Long) xM = Space(((xLength / 2) - (Len(xInput) / 2) + 1)) + xInput CenterString = xM + Space(xLength - Len(xM)) End Function An alterntive Function CenterString(xInput As String, xLength As Long) Dim xM As Long xM = Int((xLength - Len(xInput)) / 2) CenterString = Space(xM) & xInput & Space(xLength - Len(xInput) - xM) End Function -- HTH Bob Phillips ...

Users v3
Continued from thread at http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.crm&mid=2f4ffeb1-1ca5-4d56-ab3b-9e04390b4ef8&sloc=en-us Is it possible to get SID of AD user and put it to CRM database to continue use of changed user credentials??? "Peter Lynch" wrote: > CRM permissions are linked to the SID of the user's AD account, not the > username (logon name) > > The username (logon name) can be changed anytime > > The SID never changes > > > > > > "Paul&q...