Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel 2007 - Compare / Calculate


(!)

mamcelr's Avatar
mamcelr mamcelr is offline
Member with 28 posts.
THREAD STARTER
 
Join Date: Oct 2011
Experience: Low Intermediate
31-Dec-2011, 02:40 PM #31
Hans . . . please don't spend too much time, or any time, on the columns. I am perfectly happy with what I have now and I was able to learn enough about VBA to add 3 new columns myself. The only reason I asked the question was because of something you said in Post #22 about "adding an extra dimension" to the results. I thought maybe I was limited in some way on the number of columns of information that could be written to "Delta".

If you spend anymore time on this, it would be more helpful to identify the new notes in column "A" of "Delta" with an "N".

Thanks so much for your help.

Mac
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,430 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
31-Dec-2011, 07:31 PM #32
Happy New Year, all you need to do for the "N" value is add the red line of code in the module

Code:
WriteDelta:
dRow = dRow + 1
If myLoop = 1 Then
    wsD.Cells(dRow, "B") = Rng2.Value
    wsD.Cells(dRow, "C") = Rng2.Offset(0, 1)
    wsD.Cells(dRow, "E") = Rng2.Offset(0, 3)
    wsD.Cells(dRow, "F") = Rng2.Offset(0, 4)
    wsD.Cells(dRow, "G") = Rng2.Offset(0, 5)
    wsD.Cells(dRow, "J") = Rng2.Offset(0, 2)
    If rRow <= 0 Then
        wsD.Cells(dRow, "A") = "N"
        wsD.Cells(dRow, "D") = Rng2.Offset(0, 2)
        wsD.Cells(dRow, "I") = 0
    Else
        wsD.Cells(dRow, "D") = Rng2.Offset(0, 2) - ws1.Cells(rRow, "D").Value
        wsD.Cells(dRow, "I") = ws1.Cells(rRow, "D").Value
    End If
ElseIf myLoop = 2 Then
    wsD.Cells(dRow, "A") = "C"
    wsD.Cells(dRow, "B") = Rng1.Value
    wsD.Cells(dRow, "C") = Rng1.Offset(0, 1)
    wsD.Cells(dRow, "D") = Rng1.Offset(0, 2) * -1
    wsD.Cells(dRow, "E") = Rng1.Offset(0, 3)
    wsD.Cells(dRow, "F") = Rng1.Offset(0, 4)
    wsD.Cells(dRow, "G") = Rng1.Offset(0, 5)
    wsD.Cells(dRow, "I") = Rng1.Offset(0, 2)
    wsD.Cells(dRow, "J") = 0
End If
mamcelr's Avatar
mamcelr mamcelr is offline
Member with 28 posts.
THREAD STARTER
 
Join Date: Oct 2011
Experience: Low Intermediate
31-Dec-2011, 09:07 PM #33
Oh my gosh! That's too easy . . . that will take care of everything.

Thank you again for your help and patience. Happy new year to you and all the best in 2010.

Mac
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,430 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
01-Jan-2012, 05:23 AM #34
Quote:
Originally Posted by mamcelr View Post
Oh my gosh! That's too easy . . . that will take care of everything.

Thank you again for your help and patience. Happy new year to you and all the best in 2010.

Mac
Hi Mac,

You're now two years behind. I'll take 2012 it it's okay with you

All the best too, but for 2012 and on

Last edited by Keebellah; 01-Jan-2012 at 05:24 AM.. Reason: Type-Oooo
mamcelr's Avatar
mamcelr mamcelr is offline
Member with 28 posts.
THREAD STARTER
 
Join Date: Oct 2011
Experience: Low Intermediate
01-Jan-2012, 01:02 PM #35
Yikes! Tells you how my new years eve was golng. I was sitting in an emergency room with my wife who had taken a fall and broken her elbow and wrist. She was a trooper though and still went to a party, ringing in the new year supported by drugs.

You deserve the extra 2 years given everything you did for me! Use it wisely :-)
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,430 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
01-Jan-2012, 05:13 PM #36
Hi Mac,
I hope your wife's doing well now that the drugs have worked off.
I did some coding (even if you said you didn't need it) and now the extra columns will be added automatically and veryhting moved accordingly.
You added the three extra columns and that was perfect but if more come the formula's and layout needed handwork.
If I got it right this will all go automatically.
The Delta sheet is rebuilt every time and the extra columns can be added at will.
I did not however add a contro in cale you add more than 16384 columns whihc is Excel's limit for version 2007 and up

All the best again for 2012.
Attached Files
File Type: xlsm 1283 Change in Balance Corrected-03.xlsm (119.6 KB, 39 views)
mamcelr's Avatar
mamcelr mamcelr is offline
Member with 28 posts.
THREAD STARTER
 
Join Date: Oct 2011
Experience: Low Intermediate
01-Jan-2012, 09:06 PM #37
Hans,

Thanks for asking, she's pretty sore right today. I think she was running on adrenaline last might . . . it's worn off now.

The ability to add columns is great! It was good for me to try and figure out how to do it myself, but it was tedious. I'll try not to add more than 16,384 columns ;-)

When I went to play with the workbook this morning, the year had changed and the code was looking for worksheet names of "2011" and "2012", just like you said it would. Unfortunately we don't get the data to populate this workbook until 3-4 days after month-end, so I'll still be looking at the comparison between 2010 and 2011 in 2012. No big deal, I just changed your code to look for worksheet names of "Prior Year End" and "Year To Date" and named the worksheets the same. Still works great!!

Here's to a great 2012

Mac
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,430 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
02-Jan-2012, 02:07 AM #38
Well Mac,

You beat me to, I was starting to incorporate the dialog to enter desired year rigth after I setn you the file.
Here is my finished version
All the best.
Attached Files
File Type: xlsm 1283 Change in Balance Corrected-03.xlsm (123.1 KB, 49 views)
mamcelr's Avatar
mamcelr mamcelr is offline
Member with 28 posts.
THREAD STARTER
 
Join Date: Oct 2011
Experience: Low Intermediate
02-Jan-2012, 01:07 PM #39
Hans . . . I wouldn't worry about the "student" getting a jump on the "teacher". As usual, your solution is much better than mine!

The edits are very much appreciated.

Take care,

Mac
mamcelr's Avatar
mamcelr mamcelr is offline
Member with 28 posts.
THREAD STARTER
 
Join Date: Oct 2011
Experience: Low Intermediate
02-Jan-2012, 05:16 PM #40
Hans,

Sorry to bother you. I have a "Sub or Function not defined" compile error occuring after I copied the macro into my active file. It has to do with "C2R" and appears first in the following locale, then throughout the macro:

' Rebuild Delta sheet
GoSub DeltaColumns
dRow = 2
wsD.Cells(2, C2R(lstcol + 2)) = ws1.Name ' I
wsD.Cells(2, C2R(lstcol + 3)) = ws2.Name ' J

I'm cannot find where C2R is defined?

Mac
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,430 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
02-Jan-2012, 05:25 PM #41
You forgot to copy the other module it's the one that defines the column
Keebellah's Avatar
Keebellah   (Hans) Keebellah is offline Keebellah is a Trusted Advisor with special permissions. Keebellah has a Profile Picture
Computer Specs
Trusted Advisor with 5,430 posts.
 
Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
Experience: Advanced
03-Jan-2012, 01:05 AM #42
Did you get it working? The module you forgot is named C2RMod
mamcelr's Avatar
mamcelr mamcelr is offline
Member with 28 posts.
THREAD STARTER
 
Join Date: Oct 2011
Experience: Low Intermediate
03-Jan-2012, 01:18 AM #43
Found it, finally. Thanks for the tip. I didn't know where to look for it. Sorry about that.

And thanks again!!
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
calculate, compare, compare range, consolidate, excel

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑