[VBA] SUMIF coding problem with a file

It would probably help if you posted a sample workbook with some typical data demonstrating the problem.


Regards, TMS

That's not a sample workbook ...

Why? Because I have no plans to set up an empty workbook, copy your data into it, try and format it like the real thing, and then copy and paste your code into it so I can test and diagnose your problem. And, if it works for me, all that has been proved is that it is something to do with your data. That's why.

Your problem, and you're on your own with it if you can't be bothered helping others to help you.

Don't expect any further input from me.

OK, that's not anger you are "hearing", it is frustration. You're wasting my time because you won't help me to help you. And it's wasting your time because you're not getting the solution that you need.

You have provided some "samples" of the data. That data could exist pretty much anywhere on a worksheet, but we can perhaps assume that it starts in the top left cell, cell A1, and the headings are in row 1, in cells A1 and B1. That said, only recently, someone wanted a Dynamic Named Range that appeared (from the formula quoted) to start in cell A1. In reality, it started in cell F190. Easy mistake, yes?

You've provided an excerpt of your code, splitting it into three sections so it becomes a chore to copy. It doesn't have a Sub or End Sub, it doesn't define the variables, it doesn't indicate where the variables have values set, for example, the workbook name and the value of the variable "i". There's no indication of what you want/need to do with the value when you get it.

I don't know if you use Option Explicit to ensure that all variables are defined, but I do because it avoids typing mistakes, etc.

So, as I said,for me to test your code, I need to create a workbook; I need to copy the data from your next post into it; I need to use Text to Columns to split the data into columns A and B. For convenience, I then put a SUMIF formula into the worksheet to check the value I expect to get from the code.

That's before I even look at the code. I need to copy the three sections and paste them into a module with an empty Subroutine. I need to comment out the explanations that you have provided about what works and what doesn't. Then I have to define all the variables correctly and, where possible, set the values of the variables so the code will run. At that point, I have a workbook with some data and some code in it ... that runs and produces a value.

What I don't have is a workbook with code in it that opens another workbook with data in it and then calculates a value.

Hopefully, that explains my frustration.

The working code, which does not open a second workbook and which uses data within the macro enabled workbook is listed below.

Please Login or Register  to view this content.

Please see the attached workbook. You may be able to adapt this to meet your needs.

Regards, TMS

The problem appears to be that it doesn't like doing a SUMIF in a separate instance of Excel. Removing the Excel_app fixes the problem. ScreenUpdating = False masks the opening of the Summary workbook in the same instance.

Please Login or Register  to view this content.

The original question has been answered. You should mark this solved and start a new thread.

The current code updates the summary sheet in the workbook you navigate to.

You need to post sample workbooks which accurately reflect your requirements.