Search Search for: Business ApplicationsAll Forums

# Solved: Excel 2007 - Compare / Calculate

Member with 28 posts.

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   (Hans)

Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
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```
Member with 28 posts.

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   (Hans)

Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
01-Jan-2012, 05:23 AM #34
Quote:
 Originally Posted by mamcelr 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
Member with 28 posts.

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   (Hans)

Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
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
 1283 Change in Balance Corrected-03.xlsm (119.6 KB, 18 views)
__________________
Cheers,
Hans
It's all in the code... and may the code be with you! If it isn't . . . start debugging! (Time zone: Western Europe)
Member with 28 posts.

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   (Hans)

Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
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
 1283 Change in Balance Corrected-03.xlsm (123.1 KB, 31 views)
Member with 28 posts.

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
Member with 28 posts.

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   (Hans)

Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
02-Jan-2012, 05:25 PM #41
You forgot to copy the other module it's the one that defines the column
 Keebellah   (Hans)

Join Date: Mar 2008
Location: Oegstgeest, The Netherlands
03-Jan-2012, 01:05 AM #42
Did you get it working? The module you forgot is named C2RMod
Member with 28 posts.

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!!
 techguy.org/1023892
As Seen On

WELCOME TO TECH SUPPORT GUY!

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

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)