Thanks Dilip. I was trying to find something similar in other posts but nothing about sumproduct with external file. My main problem is how to store the path name for the file selected by the user in a way that sumproduct can understand, store the first worksheet name, and pass both variables to the sumproduct.... can not make it work :(
Date: Sat, 30 Jan 2010 23:46:46 +0530 Subject: Re: SumProduct in VBA - Error From: dilipan...@gmail.com To: cecic...@hotmail.com CC: excel-macros@googlegroups.com Dear Cecilia, I have tried the code given by you and it is asking for a file. And since I do not have that file, I have gone ahead and found following piece of information which would help you out. http://www.ozgrid.com/forum/showthread.php?t=16044 According to me, the double quotes which you have used in the formula might be one of the show stopper. I have attached an example to use SUMPRODUCT via VBA, as the image which might help you out. Best of Luck. Thanks & Regards, -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 110062 On 1/29/10, Cecilia Chiderski <cecic...@hotmail.com> wrote: Dilip, I apologyze for bothering you... but I am getting crazy with this. Don't worry, I should finish my project for tomorrow or I will be fired so no more projects :) I was just dealing with a formula for the last five hours, and can not understand what's wrong. Are you familiar with SumProduct in VBA?? Would you mind taking a quick look on my formula to see if you can find the error??? I added "", removed them, tried everything and doesn't work!!! It returns #value error, but if I enter the formula directly in Excel, it works perfect. Thanks a lot! Cecilia Sub testingnew() Set fd = Application.FileDialog(msoFileDialogOpen) With fd .AllowMultiSelect = False If .Show = -1 Then Set filesource = Workbooks.Open(Filename:=fd.SelectedItems(1)) With filesource ws = .ActiveSheet.Name End With ThisWorkbook.Activate ThisWorkbook.Worksheets("Tracking").Range("AF13").Select ActiveCell.Offset(0, Sheets("Tracking").Range("D297")).Range("A1") = Evaluate("SumProduct(--(&filesource&ws$T$2:$T$43735) = ""Fixed Fee""), --((&filesource&ws$O$2:$O$43735) = AN6), --(&filesource&ws$AH$2:$AH$43735))") End If End With End Sub ¡Nuevo MSN Deportes! Sigue los partidos en directo y encuentra la última información de tus equipos favoritos. _________________________________________________________________ ¡Seducción! 249 historias cada semana en el sitio nº1 para conseguir una cita. ¡Regístrate! http://contactos.es.msn.com/?mtcmk=015352 -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
