1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2007 - Compare / Calculate

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

Thread Status:
Not open for further replies.
Advertisement
  1. mamcelr

    mamcelr Thread Starter

    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
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    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
    
    
     
  3. mamcelr

    mamcelr Thread Starter

    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
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    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
     
  5. mamcelr

    mamcelr Thread Starter

    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 :)
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    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:

  7. mamcelr

    mamcelr Thread Starter

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

    Mac
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    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:

  9. mamcelr

    mamcelr Thread Starter

    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
     
  10. mamcelr

    mamcelr Thread Starter

    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
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    You forgot to copy the other module it's the one that defines the column
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    Did you get it working? The module you forgot is named C2RMod
     
  13. mamcelr

    mamcelr Thread Starter

    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!!
     
  14. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1023892