Hi,
I am trying to write a macro that calculates the total of each column provided that it meets criteria in column D.
For example, I need it to SUM all of the numbers in column I provided that column D beings with a 5.
That total then needs to be shown on a different sheet in 1 cell.
I have attached a sample book.
Thanks!
Any thoughts???
here is the code I have come up with. I am having trouble doing this. Any help would be great
Sub Copy()
Dim rcnt As Long
rcnt = Range("D" & Rows.Count).End(xlDown).Row
For I = 2 To rcnt
If Range("D").Value > 50000 < 60000 Then
Range(I).Select.Copy
Selection.Paste.Sheet "Total"
End If
Next
End Sub
Is there anyway I could add another criteria and run them in the same SUMIF?
Such sumif C=1200?
Hi luke.guthrie
Please wrap your code in code tags...I'll take a look at your issue.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
HTML Code:
Thank you Sir.
Hi luke.guthrie
This code will do the 5#### series. It's in the attached in a general module. Click the "Run Me" button.
Assuming this does as you require you'll need to define your ultimate goal a bit clearer.
Please Login or Register to view this content.
Things have changed a bit. I now need the macro to calculate anything with the word "Revenue" in C54:C86.
I.e. C54, C56, C58 all are "Revenue" I need the total value of all Revenue displayed on the 'Total' sheet for that corresponding coulmn (YTD Actuals, CUR Month, etc..)
How would I change that code in order to do so?
HTML Code:
Sorry here is the appropriate workbook
Hi luke.guthrie
I don't find the word "Revenue" in your attachment.
Hmm try this book.
Hi luke.guthrie
I've no idea where you're going with this...I must be missing something. This code does as I understand your request.Please Login or Register to view this content.
Thanks for the help. I was just trying to get someone to show me how to write the code to do that much, and I'm hoping to figure out the rest from there. There is a lot more to it but i can hopefully figure the rest out.
Thanks!
Hi luke.guthrie
You're welcome...glad I could be of help. If that satisfies your need, I'd appreciate it if you'll please mark your thread as "Solved".
I need one more piece of help.
1. Is there anyway to condense this code.
2. This code is giving me an error. Compile error: expected End With.
HTML Code:
Hi luke.guthrie
For each With the code requires an End With. You have it hereBut not here and throughout the rest of the codePlease Login or Register to view this content.And yes, there are several ways to condense the code...can you share the workbook? I'll show you some ideas.Please Login or Register to view this content.
I can`t share the current workbook because of privacy issues. I`m ok with the long form I guess. Thanks for your help!
Hi luke
I understand the Privacy issues. Are we ALWAYS dealing with the same range (C53:C86) and are there other values in that range than you've listed (*Revenue 1200, *Revenue 1201, *Revenue 1202, etc)? If so, what do they look like?
Several ideas float around in my mind...applying them to data without seeing it is a bit difficult. I'm thinking we could apply a couple of loops to the problem and reduce the code dramatically.
PS: I just looked at your file again and, now that I see what YOUR code does I can offer some suggestions based on the file you posted. Get back to you.
Last edited by jaslake; 06-01-2012 at 04:06 PM.
Hi luke
If you're interested, here's what you can do.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks