How to correct my combined SumIf and SumProduct calculation in macro coding

I was summing a column that was all Canadian funds but the client has since introduced other currencies into the rows. So now to sum a column if it is canadian funds then I just sum the funds and if it is other than CDN funds then I need to multiply by the currency then sum it all as Canadian. Here is my code below. Can anyone tell me why the SumProduct function will not work properly in my code. If I do the following command in Excel columns then everything works fine but now I need to take this code

Please Login or Register  to view this content.

and incorporate it in my Excel Macro code where I look thru worksheets to sum each individual column that has numerical amounts. Am doing this to update the summary total worksheet. Here is my Excel Macro code below. I get a compile error on my new SummaryTotal field stating that "Sub or Function not defined" and it highlights the SumProduct function within my NEW SummaryTotal field calculation.

The old SummaryTotal calculation worked just fine but when foreign currencies were introduced into the rows I had to modify the calculation. I did not want to loop thru the columns manually using a loop because each worksheet has maybe 30 columns that need to be individually summed and there are maybe 20 worksheets. My logic loops thru each worksheet and then summarizes the dollar totals for each individual column to a specific cell within the YTD summary total worksheet.

How do I this modified SummaryTotal calculation to do what I need to do? Here is my macro coding below.

Please Login or Register  to view this content.

Came across this URL regarding SUMPRODUCT. Will this help what I am trying to do with the combination of SUMIF and SUMPRODUCT?

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VBA SUMPRODUCT and Worksheet SUMPRODUCT do not execute in quite the same way... generally speaking using SUMPRODUCT in VBA is done via Evaluate.

Please Login or Register  to view this content.

might be converted to:

Please Login or Register  to view this content.

The above assumes that Col J is 0 or blank where Col I is CDN else you would be double counting CDN. If Column J were set to 1 for CDN entries then you could remove the SUMIF altogether.
You don't need to use Evaluate with SUMIF of course but for sake of consistency that was adopted approach.

Note: above untested (no sample file nor time to create one!)

Thanks for your reply, will try it out.

I get a type mismatch error on the following code line. I have no idea what this offset and Address is doing in this code so maybe you could explain. I do not fully understand what you are doing.

Please Login or Register  to view this content.

I attached my spreadsheet logic. Look at the following method where I am trying to modify the code:

Please Login or Register  to view this content.

I'm afraid I don't have the time to look in depth but at first glance I suspect the issue is down to your blank sheets

You could conduct a quick fix to handle all errors...

Please Login or Register  to view this content.

How would I change ".Offset(,7)" to be a different column each time thru? I want "Offset(,7)" to be "Column_Number" Instead. Am going to play with this logic. The logic works but it stays in the same column to do the summing. So I need to take ".Offset(,7)" and replace it with "Column_Number" which in my code changes each time thru.

Each time I loop thru my columns it keeps re-adding the same column because I have Offset(,7) Is there a way to change Offset(,7) to a variable value. I have a field called Column_Number and this contains the current coloumn number of the column I want to add. I tried to modify this OFFSET logic above without much sucess. The logic you gave me works but I am currently always adding the exact same column.

Right now I am searching on the web for an example of a variable offset.

I changed the code slightly to the following and now it works the way I need it to work. I replaced the column offset with my variable ColumnNumber because this is the column where I always want to be to sum values. This variable value keeps changing as I loop thru my worksheet. Because the code is using the Offset function I had to subtract 9 from the variable ColumnNumber because the Offset assumes you are starting from the Currency column. I want the column number to be the value that is in ColumnNumber. Please let me know if you see anything wrong with my code before I mark it as resolved.

Please Login or Register  to view this content.