[mamcelr]

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]

Happy New Year, all you need to do for the "N" value is add the red line of code in the module

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]

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]

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

[mamcelr]

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]

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.

[mamcelr]

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]

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.

[mamcelr]

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]

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]

You forgot to copy the other module it's the one that defines the column

[Keebellah]

Did you get it working? The module you forgot is named C2RMod

[mamcelr]

Found it, finally. Thanks for the tip. I didn't know where to look for it. Sorry about that.

And thanks again!!