xMIKExSMITHx
New Member
- Joined
- Jul 11, 2014
- Messages
- 45
Hi all,
Please excuse me if i didnt post this the right way as it is my first time to post actual coding.
I have a rather lengthy VBA that runs reports for my general ledger. I will try to explain this without confusing myself and anyone else reading this.
I want to compare the value that generates by the code below on the "Project Specific GL(2)", which by now, has gotten renamed by my VBA above,
Against the last value in column H from the "Data - XXXXX (2)" sheet, which has also been renamed by my VBA above.
If these two values do not match, I want this to display.
if they do match, then this:
Please excuse me if i didnt post this the right way as it is my first time to post actual coding.
I have a rather lengthy VBA that runs reports for my general ledger. I will try to explain this without confusing myself and anyone else reading this.
Code:
Sub Run_Report()
ActiveSheet.Unprotect "accounting13"
ActiveWorkbook.Unprotect "accounting13"
Dim FltrLst As Range
Dim Fltr As Variant
Set FltrLst = Sheets(7).Range("A1").CurrentRegion
For Each Fltr In FltrLst
Sheets("Master GL").Visible = True
With Sheets("Master GL").Range("A1")
.AutoFilter Field:=6, Criteria1:=Fltr
End With
Sheets("Project Specific GL").Visible = True
Sheets("Project Specific GL").Select
Sheets("Project Specific GL").Copy After:=Sheets(6)
Sheets("Project Specific GL").Visible = False
Sheets("Master GL").Select
Range("A1:AA20000").Select
Selection.Copy
Sheets("Project Specific GL (2)").Select
Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Master GL").Visible = False
Sheets("Project Specific GL (2)").Select
Rows("1:1").Select
Selection.AutoFilter
Range("I:I,P:P,X:X").Select
Range("X1").Activate
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Select
Range("J:J,Y:AA").Select
Range("Y1").Activate
Selection.Style = "Comma"
Range("A1").Select
Cells.Select
Cells.EntireColumn.AutoFit
Range("F1").Select
Sheets("GL Table").Visible = True
Sheets("GL Table").Select
Sheets("GL Table").Copy After:=Sheets(6)
Sheets("GL Table").Visible = False
Sheets("Project Specific GL (2)").Select
ActiveSheet.Range("$A$1:$AZ$1000").AutoFilter Field:=10, Criteria1:=RGB(217, _
151, 149), Operator:=xlFilterCellColor
Sheets("GL Table (2)").Select
Range("C6").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'Project Specific GL (2)'!C[7])"
Range("C6").Select
Selection.Copy
Range("c6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Project Specific GL (2)").Select
ActiveSheet.Range("$A$1:$AZ$1000").AutoFilter Field:=10, Criteria1:=RGB(194, _
214, 154), Operator:=xlFilterCellColor
Sheets("GL Table (2)").Select
Range("C7").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'Project Specific GL (2)'!C[7])"
Range("C7").Select
Selection.Copy
Range("c7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Project Specific GL (2)").Select
ActiveSheet.Range("$A$1:$AZ$1000").AutoFilter Field:=10, Criteria1:=RGB(250, _
192, 144), Operator:=xlFilterCellColor
Sheets("GL Table (2)").Select
Range("C8").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'Project Specific GL (2)'!C[7])"
Range("C8").Select
Selection.Copy
Range("c8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Project Specific GL (2)").Select
ActiveSheet.Range("$A$1:$AZ$1000").AutoFilter Field:=10, Criteria1:=RGB(147, _
205, 221), Operator:=xlFilterCellColor
Sheets("GL Table (2)").Select
Range("C9").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'Project Specific GL (2)'!C[7])"
Range("C9").Select
Selection.Copy
Range("c9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Project Specific GL (2)").Select
ActiveSheet.Range("$A$1:$AZ$1000").AutoFilter Field:=10, Criteria1:=RGB(0, _
176, 80), Operator:=xlFilterCellColor
Sheets("GL Table (2)").Select
Range("C10").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'Project Specific GL (2)'!C[7])"
Range("C10").Select
Selection.Copy
Range("c10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Project Specific GL (2)").Select
ActiveSheet.Range("$A$1:$AZ$1000").AutoFilter Field:=10, Criteria1:=RGB(148, _
139, 84), Operator:=xlFilterCellColor
Sheets("GL Table (2)").Select
Range("C11").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'Project Specific GL (2)'!C[7])"
Range("C11").Select
Selection.Copy
Range("c11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Project Specific GL (2)").Select
ActiveSheet.Range("$A$1:$AZ$1000").AutoFilter Field:=10, Criteria1:=RGB(149, _
179, 215), Operator:=xlFilterCellColor
Sheets("GL Table (2)").Select
Range("C12").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'Project Specific GL (2)'!C[7])"
Range("C12").Select
Selection.Copy
Range("c12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Project Specific GL (2)").Select
ActiveSheet.Range("$A$1:$AZ$1000").AutoFilter Field:=10, Criteria1:=RGB(83, _
142, 213), Operator:=xlFilterCellColor
Sheets("GL Table (2)").Select
Range("C13").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'Project Specific GL (2)'!C[7])"
Range("C13").Select
Selection.Copy
Range("c13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Project Specific GL (2)").Select
ActiveSheet.Range("$A$1:$AZ$1000").AutoFilter Field:=10, Criteria1:=RGB(178, _
161, 199), Operator:=xlFilterCellColor
Sheets("GL Table (2)").Select
Range("C14").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'Project Specific GL (2)'!C[7])"
Range("C14").Select
Selection.Copy
Range("c14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("c15").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
Columns("c:c").ColumnWidth = 14
Columns("c:c").Select
Selection.Style = "Comma"
Range("a1").Select
Sheets("Project Specific GL (2)").Select
ActiveSheet.Range("$A$1:$Y$186").AutoFilter Field:=10
Range("a1").Select
Sheets("GL Table (2)").Select
Range("a1").Select
Sheets("Data - XXXXX").Visible = True
Sheets("Data - XXXXX").Select
Sheets("Data - XXXXX").Copy After:=Sheets(6)
Sheets("Data - XXXXX").Visible = False
Sheets("Data - XXXXX (2)").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=""Sponsor: ""&VALUE(RIGHT('Project Specific GL (2)'!R[1]C[5],10))&"" ""&VLOOKUP(VALUE('Project Specific GL (2)'!R[1]C[5]),CHOOSE({1,2},MML!R[1]C[15]:R[3000]C[15],MML!R[1]C[2]:R[3000]C[2]),2,0)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("a2").Select
ActiveCell.FormulaR1C1 = _
"=""Pass-Through costs incurred from: ""&VLOOKUP('project specific gl (2)'!RC[1],'GL Table'!C5:C6,2,FALSE)&"" ""&'project specific gl (2)'!RC"
Range("A8").Select
ActiveCell.FormulaR1C1 = _
"=IF('Project Specific GL (2)'!R[-6]C[11]=""vendor"",'Project Specific GL (2)'!R[-6]C[14],IF('Project Specific GL (2)'!R[-6]C[11]=""fedex"",'Project Specific GL (2)'!R[-6]C[14],IF('Project Specific GL (2)'!R[-6]C[11]=""travel"",'Project Specific GL (2)'!R[-6]C[20],IF('Project Specific GL (2)'!R[-6]C[11]=""canada"",'Project Specific GL (2)'!R[-6]C[20],IF('Project Specific GL (2)'!R[-6]C[11]=""pnet"",""Pharmanet Pass-Through Costs"",IF('Project Specific GL (2)'!R[-6]C[11]=""Latin America"",""i3 Latin America Costs"",IF('Project Specific GL (2)'!R[-6]C[11]=""ROW"",""InVentiv Health Clinical UK-International Costs"",""x"")))))))"
Range("b8").Select
ActiveCell.FormulaR1C1 = _
"=IF('Project Specific GL (2)'!R[-6]C[10]=""vendor"",""United States"",IF('Project Specific GL (2)'!R[-6]C[10]=""fedex"",""United States"",IF('Project Specific GL (2)'!R[-6]C[10]=""travel"",""United States"",IF('Project Specific GL (2)'!R[-6]C[10]=""canada"",""Canada"",IF('Project Specific GL (2)'!R[-6]C[10]=""Pnet"",""Various Locations"",IF('Project Specific GL (2)'!R[-6]C[10]=""latin america"",""Latin America"",IF('Project Specific GL (2)'!R[-6]C[10]=""ROW"",""Various Locations"","""")))))))"
Range("c8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK('Project Specific GL (2)'!R2C6),"""",IF('Project Specific GL (2)'!R[-6]C[9]>0,VALUE(VLOOKUP('Project Specific GL (2)'!R[-6]C[-1],'GL Table (2)'!R5C5:R16C6,2,FALSE)&'Project Specific GL (2)'!R[-6]C[-2])))"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"=IF('Project Specific GL (2)'!R[-6]C[8]=""vendor"",'Project Specific GL (2)'!R[-6]C[12],IF('Project Specific GL (2)'!R[-6]C[8]=""fedex"",'Project Specific GL (2)'!R[-6]C[12],IF('Project Specific GL (2)'!R[-6]C[8]=""travel"",'Project Specific GL (2)'!R[-6]C[20],IF('Project Specific GL (2)'!R[-6]C[8]=""Canada"",'Project Specific GL (2)'!R[-6]C[20],IF('Project Specific GL (2)'!R[-6]C[8]=""Pnet"",'Project Specific GL (2)'!R[-6]C[5],IF('Project Specific GL (2)'!R[-6]C[8]=""latin america"",'Project Specific GL (2)'!R[-6]C[5],IF('Project Specific GL (2)'!R[-6]C[8]=""ROW"",'Project Specific GL (2)'!R[-6]C[5],"""")))))))"
Range("E8").Select
ActiveCell.FormulaR1C1 = _
"=IF('Project Specific GL (2)'!R[-6]C[7]=""Vendor"",'Project Specific GL (2)'!R[-6]C[9],IF('Project Specific GL (2)'!R[-6]C[7]=""fedex"",'Project Specific GL (2)'!R[-6]C[9],IF('Project Specific GL (2)'!R[-6]C[7]=""travel"",'Project Specific GL (2)'!R[-6]C[17],IF('Project Specific GL (2)'!R[-6]C[7]=""canada"",'Project Specific GL (2)'!R[-6]C[17],IF('Project Specific GL (2)'!R[-6]C[7]=""Pnet"",'Project Specific GL (2)'!R[-6]C[8],IF('Project Specific GL (2)'!R[-6]C[7]=""ROW"",""ROW ""&LEFT(VLOOKUP('Project Specific GL (2)'!R[-6]C[-3],'GL Table (2)'!R5C5:R16C6,2,FALSE),3)&""'""&RIGHT('Project Specific GL (2)'!R[-6]C[-4],2)&"" Pass Through Costs"",IF('Project Specific GL (2)'!R[-6]C[7]=""Latin America"",""LA ""&LEFT(VLOOKUP('Project Specific GL (2)'!R[-6]C[-3],'GL Table (2)'!R5C5:R16C6,2,FALSE),3)&""'""&RIGHT('Project Specific GL (2)'!R[-6]C[-4],2)&"" Pass Through Costs"","""")))))))"
Range("F8").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[5],'Types of Expense'!R2C2:R93C3,2,FALSE)"
Range("G8").Select
ActiveCell.FormulaR1C1 = _
"=IF('Project Specific GL (2)'!R[-6]C[5]=""vendor"",'Project Specific GL (2)'!R[-6]C[3],IF('Project Specific GL (2)'!R[-6]C[5]=""fedex"",'Project Specific GL (2)'!R[-6]C[3],IF('Project Specific GL (2)'!R[-6]C[5]=""travel"",'Project Specific GL (2)'!R[-6]C[3],IF('Project Specific GL (2)'!R[-6]C[5]=""canada"",'Project Specific GL (2)'!R[-6]C[19],IF('Project Specific GL (2)'!R[-6]C[5]=""pnet"","""",IF('Project Specific GL (2)'!R[-6]C[5]=""latin america"","""",IF('Project Specific GL (2)'!R[-6]C[5]=""ROW"",'Project Specific GL (2)'!R[-6]C[19],"""")))))))"
Range("H8").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]<>RC[1],VLOOKUP(RC[-6],'Types of Expense'!C5:C6,2,FALSE),""USD"")"
Range("I8").Select
ActiveCell.FormulaR1C1 = _
"='Project Specific GL (2)'!R[-6]C[1]"
Range("J8").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[4]>0,RC[1]&RC[2]&RC[3]&R7C14&RC[4]&R7C15&RC[5],RC[1]&RC[2]&RC[3])"
Range("K8").Select
ActiveCell.FormulaR1C1 = _
"=IF('Project Specific GL (2)'!R[-6]C[1]=""vendor"", VLOOKUP(RC[-10],'Types of Expense'!R2C8:R1000C9,2,false),IF('Project Specific GL (2)'!R[-6]C[1]=""fedex"",""Shipping/Postage"",IF('Project Specific GL (2)'!R[-6]C[1]=""travel"",'Project Specific GL (2)'!R[-6]C[12],IF('Project Specific GL (2)'!R[-6]C[1]=""canada"",'Project Specific GL (2)'!R[-6]C[12],IF('Project Specific GL (2)'!R[-6]C[1]=""pnet"",""See Supporting Documentation"",IF('Project Specific GL (2)'!R[-6]C[1]=""Latin America"",""See Supporting Documentation"",""See Supporting Documentation""))))))"
Range("L8").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR('Project Specific GL (2)'!R[-6]C=""travel"",'Project Specific GL (2)'!R[-6]C=""canada""),""-"","""")"
Range("M8").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR('Project Specific GL (2)'!R[-6]C[-1]=""travel"",'Project Specific GL (2)'!R[-6]C[-1]=""canada""),'Project Specific GL (2)'!R[-6]C[7],"""")"
Range("A8:P8").Select
Selection.AutoFill Destination:=Range("A8:P1000"), Type:=xlFillDefault
Range("A8:P1000").Select
Range("A8").Select
Range("A1").Select
Range("A1:P2000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SEARCH(""*total*"",$E1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SEARCH(""*grand total*"",$e1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A7:P1000").Select
Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Dim i As Long
For i = 1500 To 8 Step -1
If Range("A" & i).Value = "x" Then Rows(i).Delete
Next i
Sheets("Data - XXXXX (2)").Name = Right(Sheets("Project Specific GL (2)").Range("F2").Value, 10) & " - Data"
Range("A8").Select
Range("A1").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
Columns("K:P").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Cells.Replace What:="#N/A", Replacement:="Other CR Expenses", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A8").Select
Application.CutCopyMode = False
Range("A1").Select
Dim j As Long
For j = 1500 To 8 Step -1
If Range("E" & j).Value = "Grand Total" Then Rows(j).Delete
Next j
Dim LR As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
Range("H" & LR + 1).Formula = "=SUMIF(C[-7],""<>"",C)"
LR = Range("E" & Rows.Count).End(xlUp).Row
Range("E" & LR + 1).Formula = "Grand Total"
Sheets("GL Table (2)").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Project Specific GL (2)").Select
Sheets("Project Specific GL (2)").Name = Right(Sheets("Project Specific GL (2)").Range("F2").Value, 10) & " - GL"
Range("a8").Select
Range("a1").Select
End Sub
I want to compare the value that generates by the code below on the "Project Specific GL(2)", which by now, has gotten renamed by my VBA above,
Code:
Dim S As Long
S = Range("J" & Rows.Count).End(xlUp).Row
Range("J" & S + 1).Formula = "=SUMIF(C[2],""Vendor"",C)+SUMIF(C[2],""fedex"",C)+SUMIF(C[2],""travel"",C)+SUMIF(C[2],""canada"",C)+SUMIF(C[2],""pnet"",C)+SUMIF(C[2],""latin america"",C)+SUMIF(C[2],""kcr"",C)+SUMIF(C[2],""row"",C)+SUMIF(C[2],""adjustment"",C)"
Dim T As Long
T = Range("i" & Rows.Count).End(xlUp).Row
Range("i" & T + 1).Formula = "GL Total"
Against the last value in column H from the "Data - XXXXX (2)" sheet, which has also been renamed by my VBA above.
If these two values do not match, I want this to display.
Code:
MsgBox "Totals Do Not Match", vbCritical
if they do match, then this:
Code:
MsgBox "Finished", vbInformation