Solved: Excel 2007 - Compare / Calculate

Discussion in 'Business Applications' started by mamcelr, Oct 25, 2011.

Not open for further replies.

Joined:
Oct 24, 2011
Messages:
39
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

Joined:
Mar 27, 2008
Messages:
6,339
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
[B][COLOR="Red"]        wsD.Cells(dRow, "A") = "N"[/COLOR][/B]
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

```

Joined:
Oct 24, 2011
Messages:
39
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

Joined:
Mar 27, 2008
Messages:
6,339
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

Joined:
Oct 24, 2011
Messages:
39
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

Joined:
Mar 27, 2008
Messages:
6,339
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
File size:
119.6 KB
Views:
55

Joined:
Oct 24, 2011
Messages:
39
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

Joined:
Mar 27, 2008
Messages:
6,339
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
File size:
123.1 KB
Views:
68

Joined:
Oct 24, 2011
Messages:
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

Joined:
Oct 24, 2011
Messages:
39
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

Joined:
Mar 27, 2008
Messages:
6,339
You forgot to copy the other module it's the one that defines the column

Joined:
Mar 27, 2008
Messages:
6,339
Did you get it working? The module you forgot is named C2RMod

Joined:
Oct 24, 2011
Messages:
39
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