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:
>>> use code - tags <<<
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...
>>> 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
Last edited by a moderator: