JM_Brazil
09-05-2012, 12:24 PM
Hi all, übernoob here, really enjoying this forum, and am learning a great deal from it. Thanks for that!
Doubt - probably simple for folks here, but have smoke coming out of my ears trying to solve it.
I extract frequent reports from an external application (Salesforce) into an Excel worksheet. After some basic macros, I format the worksheet to my liking, but the number of rows vary from report to report. Part of the automation I have created is to take all Customer entries in column A (Column A = customer names, many repetitive), and create a list of individual customers in alphabetical order. I would now like to use this list as my criteria for Sumif.
Again, my dilema is that there is no set length to this report. I would like to use the first customer entry as the first criteria of my first Sumif.
If I use the actual cell as a reference, it works, as follows;
Finalrow = Range("B65536").End(xlUp).Row
Range("b" & Finalrow + 3).Formula = "=SUMIF(a3:a" & Finalrow & ",A250,b3:b" & Finalrow & ")"
Here as you can see I use column A as the lookup range, and column B as the sum range, and cell A250 as my criteria (customer name). Although always in col.A, Cell A250 changes from report to report.
How can I incorporate a variable reference into this formula? For example, I've been trying to use something like this;
MyRange = Range("A1").End(xlDown).Offset(3, 0).Select
(Finds the last row of my table, offsets 3 rows down which is the beginning of my customer list to use as Criteria)
But it doesn't work. Is my question understandable?
So many thanks in advance for your help!
Best regards.
Doubt - probably simple for folks here, but have smoke coming out of my ears trying to solve it.
I extract frequent reports from an external application (Salesforce) into an Excel worksheet. After some basic macros, I format the worksheet to my liking, but the number of rows vary from report to report. Part of the automation I have created is to take all Customer entries in column A (Column A = customer names, many repetitive), and create a list of individual customers in alphabetical order. I would now like to use this list as my criteria for Sumif.
Again, my dilema is that there is no set length to this report. I would like to use the first customer entry as the first criteria of my first Sumif.
If I use the actual cell as a reference, it works, as follows;
Finalrow = Range("B65536").End(xlUp).Row
Range("b" & Finalrow + 3).Formula = "=SUMIF(a3:a" & Finalrow & ",A250,b3:b" & Finalrow & ")"
Here as you can see I use column A as the lookup range, and column B as the sum range, and cell A250 as my criteria (customer name). Although always in col.A, Cell A250 changes from report to report.
How can I incorporate a variable reference into this formula? For example, I've been trying to use something like this;
MyRange = Range("A1").End(xlDown).Offset(3, 0).Select
(Finds the last row of my table, offsets 3 rows down which is the beginning of my customer list to use as Criteria)
But it doesn't work. Is my question understandable?
So many thanks in advance for your help!
Best regards.