Attached is an inventory report I run out of SAP. Column A contains the storage location numbers for each part number(Column H). I would like to create a macro that calculates the total for all storage locations. The issue I have is that the number of storage locations is constantly changing. The totals are in column B in the same row the part number and part information is listed. Can someone help me?
Hi Scarlett,
Before I start playing with this, why is row 7 between the Before and After sheets different?
Second. Does it need to look exactly like your "After" sheet?
Third. What do you need to know from the after sheet? What questions do you answer from the after?
Forth - Did you see the grand totals at the bottom of the before and after aren't equal? Is this different data?
One test is worth a thousand opinions.
Maybe:
Please Login or Register to view this content.
So I'm hoping your before and after are two different reports? Because none of the values seem the same. I'm assuming you just wanted to show examples of the formatting you wanted. Also the before sheet is shifted over one column from the after. I didn't alter that with my code, but we certainly could.
Please Login or Register to view this content.
Hi John,
I read your code with great interest. I'm curious where you learned your VBA.
I've never seen the (3) (-2) at the end of a range selection statement before as in this line:
x = Range("C" & Rows.Count).End(3)(-2).Row
Also it was very interesting to see a ".Areas" work down the columns as I'd not seen that before.
Last I hadn't seen the Address(False, False) syntax used in any code before either.
I've only seen one other person use the first of the examples above. Where did you get your VBA training? There may be lots I'm missing in my education.
The code I tried didnt work. It gave me an error at the"For Each numrang In..." Row. Marvin, I am attaching a revised spreadsheet. I must have made a mistake
Hi Scarlett,
The new .xls file attached has no blank row at the top. It also has no before and after sheets. What I'm really looking for is what your SAP dump looks like and what you need to know or calculate from it. I'm thinking a pivot table would be most useful but don't want to start on it until I know what problem you're trying to solve. I also need to know if it needs to look exactly like your After sheet.
Thank you so much everyone!!! The last macro is just what I needed!!!!!!
@ MarvinP - I've learned from reading some, but most of what I've learned was from Forums, and Googling to solve problems.
x = Range("C" & Rows.Count).End(3)(-2).Row (3) - is a shorter version of (xlUp), (-2) is a row offset
.Address(False, False) - it removes the dollars signs in the code.
@ scarlett: You change columns from your original sample.
Maybe:
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