Compare values with message box

X

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.

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
 
You must log in or register to reply here.

Similar threads

R
  • Solved
easy Sumproduct giving value error
2
Replies
11
Views
221
RoryA
RoryA
C
  • Solved
Please help me clean up my VBA script, I'm having trouble with redundancies
Replies
4
Views
497
CapRavOr
C
C
  • Question Question
Filter or Index function to match and bring the values
Replies
1
Views
76
bebo021999
B
I
  • Solved
Compile error Variable not defind message advice
Replies
2
Views
78
ipbr21054
I
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
585
Sean15
Sean15

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top