• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA ASSISTANCE - LOST

KhodrK1590

New Member
Hello everyone,

My friend and I created a VBA - attaching it here, where it pulls into two documents, and compares the two documents based on a schedule ID which is the first highlighted row. So, I match the rows, and then it highlights things as red that do not match based on every equipment/schedule.

However, now we need to add stuff and we have no idea how to go about it and I started it but it's 100% wrong.

If anyone can help me where to add these or how to add these, I'd love you forever!

I will explain:

Basically, after this compares two sheets, and makes sheet 1 the main sheet.

After it is done comparing I have been doing this manually which is super slow:
  • Add 6 columns at the end of sheet one (I can add them, just need the foundation basically)
    • Columns AK, AL, AM, AN, AO, and AP
    • Column Headers beginning row 2:
      • Remaining Term
      • Number of payments
      • Number of full months
      • Monthly cash payment in transactional currency (TC)
      • Calculated Lease Liability in TC
      • Difference in TC
  • Add Formulas to these columns starting row 3 and all the way down based on how many rows are filled
    • AK =DATEDIF(AK$1,G3+1,"M")&"M " & DATEDIF(AK$1,G3+1,"MD")&"D""
    • AL =IF(RIGHT(AK3,3)"0D",DATEDIF($AK$1,H3+1,"M"),DATEDIF($AK$1,H3+1,"M")+1)"
    • AM"=IF(RIGHT(AK3,3)="0D",DATEDIF(AK$1,H3+1,"M"),DATEDIF(AK$1,H3+1,"M")+DATEDIF(AK$1,H3+1,"MD")/31)"
    • AO =PV(AC3/12,AM3,-AN3,,1)"
    • AP =AG3-AO3"
    • AN = should be a VLOOKUP, no idea how to do this, but it is based on A3 and it is pulling up a specific amount for the other equations
  • All of these equations work because at the top of AK1, in the row, the date 9/1/21 is, would this be easier to make if I do a separate button in the file to add a specific date?
  • I have specific columns in sheet 1, let's say, Column 6 = Column 7 in other sheet, if they are +15/-15 days, they should not be flagged red, and same for Co. 8 = 8...
Let me know if any of this makes sense, and what anyone can help me with. I have a lot of trouble with this and this is what I have so far but a lot of red...

>>> use code - tags <<<
Code:
Sub DoEverything()
  With ws
    AddColumns
        'Inserts Six Columns at AK and AP
        Worksheets(1).Range("AK:AP").EntireColumn.Insert
    AddHeader
        Worksheets(1).Range("AK2").Formula = "Remaining term"
        Worksheets(1).Range("AL2").Formula = "Number of payments"
        Worksheets(1).Range("AM2").Formula = "Number of full months"
        Worksheets(1).Range("AN2").Formula = "Monthly cash payment in transactional currency (TC)"
        Worksheets(1).Range("AO2").Formula = "Calculated Lease Liability in TC"
        Worksheets(1).Range("AP2").Formula = "Difference in TC"
    AddFormula
        'Inserts specific formulae to cells AK and AP
        Dim Formulas(1 To 6) As Variant
    With ThisWorkbook.Worksheets("Transposed Data")
        Formulas(1) = "=DATEDIF(AK$1,G3+1,"M")&"M " & DATEDIF(AK$1,G3+1,"MD")&"D""
        Formulas(2) = "=IF(RIGHT(AK3,3)=" 0D",DATEDIF($AK$1,H3+1,"M"),DATEDIF($AK$1,H3+1,"M")+1)"
        Formulas(3) = "=IF(RIGHT(AK3,3)=" 0D",DATEDIF(AK$1,H3+1,"M"),DATEDIF(AK$1,H3+1,"M")+DATEDIF(AK$1,H3+1,"MD")/31)"
        Formulas(5) = "=PV(AC3/12,AM3,-AN3,,1)"
        Formulas(6) = "=AG3-AO3"
        .Range("AK3:AP3").Formula = Formulas
'Changes number format in Columns B and C to general
        .Range("AK:AP").NumberFormat = "General"
    End With
    FillColumn
End Sub
 

Attachments

  • File Matching Template.xlsm
    113 KB · Views: 2
Last edited by a moderator:
It does not need to be the specific columns, like sometimes it might be missing the columns, however, I need 6 columns added, and AK1 to fill since that's where the formula goes...If I get the foundation that would be great! Like how to add 1 column, formula, etc.
 
Hello, difficult to help without the necessary source workbooks neither the exact expected result workbook attachment.​
Anyway you can start just activating the Macro Recorder and operating manually …​
 
Back
Top