Forum Discussion
Need assistance creating more complex data verification!
Update:
I have attached a link to be able to download the 2 files needed to accomplish what I'm looking for.
Using the R&M form, when you pull the data out of the GL Detail file, I would like it to also verify invoices on R&M to the GL by comparing the account code and total invoice value. If these 2 match, then a YES would be inserted into the checkbox for GL. If either the account code, or the total doesn't match, then a NO would be put into the GL box. The invoice number should 99.99% of the time be correct, but if not, then we would just manually verify anyways. I'm just trying to speed the process up drastically.
LINK: https://www.sendspace.com/filegroup/NAfQmgFxSC7KvmTbQHv%2BgQ
Attached is a accounting workbook that me and a fellow friend have been creating. We have come to a point that we need the macro to verify information in the page with a file that is opened. We currently have it set to pull data and calculate it, but comparing it needs to scan pertinent information and apply an answer to a column we are a little stumped.
When it opens the file needed, it needs to compare the invoice number to what is in the file it opens, once it finds that invoice number, it needs to verify the account code is correct (matches whats in the form, and verify the amount is correct ($)). Trying to see if someone out there can assist with this idea.
Attached is the current form we are using so you can see what it currently does. I can also attach a file for it to pull the data out if necessary.
Thanks for all the help.
'Open P&L File, and Find Account Code Sum Values from P&L
Sub Btn_PL_Totals()
'Unprotect Worksheet
ActiveSheet.Unprotect conPass
'Save SharedShared Location
SharedShared = "\\NT-1\Shared\Shared"
'Save Current Workbook, and Worksheet
Set wbCurrent = Application.ActiveWorkbook
CurrentMonth = ActiveSheet.Name
'Handle Error Resultant from GL Sheet Being Already Open on Macro Run
On Error GoTo PLOpen
'Open, and Set wbSource to GL File
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = SharedShared
.FilterIndex = 3
.Title = "Select the GL file you would like to use!"
.ButtonName = "Select"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count = 0 Then
ActiveSheet.Protect conPass
Exit Sub 'Avoid Error on Cancel
End If
Set wbSource = Workbooks.Open(Filename:=.SelectedItems(1)) 'Set wbSource to GL File
End With
'Verify wbSource is Good
If wbSource.Worksheets(1).Range("A1").Value <> "GL Detail" Or wbSource.Worksheets(1).Range("A2").Value <> "Center" Then
'Display Error Message, and End Sub
strMTitle = _
"Error: Invalid File Choice"
strMPrompt = _
"The file you have chosen does not appear to be a P&L file. Please confirm you are selecting the correct file!" & vbNewLine & _
vbNewLine & _
"If you are positive you are selecting the correct file, but are still receiving this error please contact either Michael Keller, or Brent Cochran for assistance."
MsgBox strMPrompt, vbOKOnly, strMTitle
'Exit Sub After Displaying Error Message
Exit Sub
End If
'Input Formula into "In GL:" Column under Account Code Totals
wbSourceName = wbSource.Name
wbCurrent.Activate
For Each cell In Range("X10:X29")
AccntCode = cell.Offset(, -5).Value
PL_Formula = "=sumif('[" & wbSourceName & "]PVT'!$A$5:$A$2000,""" & AccntCode & """,'[" & wbSourceName & "]Pvt'!$K$5:$K$2000)"
cell.Formula = PL_Formula
PL_Value = cell.Value
cell.Value = PL_Value
'Format Indicator Arrows Based on R&M to GL Value Comparisons
If cell.Value > cell.Offset(, -1).Value Then
With cell
.Offset(, 1).Value = ChrW(8593)
.Offset(, 1).Font.Color = RGB(0, 200, 0)
End With
ElseIf cell.Value < cell.Offset(, -1).Value Then
With cell
.Offset(, 1).Value = ChrW(8595)
.Offset(, 1).Font.Color = RGB(255, 0, 0)
End With
Else
With cell
.Offset(, 1).Value = vbNullString
.Offset(, 1).Font.Color = RGB(0, 0, 0)
End With
End If
Next
'Reprotect Sheet
ActiveSheet.Protect Password:=conPass, AllowFiltering:=True
Exit Sub
PLOpen:
'Display Error Message, and End Sub if GL File Already Open on Macro Run
strMTitle = _
"Error: File Already Open"
strMPrompt = _
"The file you have chosen appears to already be open, and you have (reasonably) decided against opening a duplicate. Please close the desired GL file before trying to open inside the R&M form." & vbNewLine & _
vbNewLine & _
"If you are encountering this issue due to a reason other than the one listed, please contact either Michael Keller, or Brent Cochran for assistance."
MsgBox strMPrompt, vbOKOnly, strMTitle
End Sub
It may help to upload the workbook with non-sensitive data. I can roughly see what your code is doing, but it would greatly help to see the data structure in each of the workbooks...
Also:
1. Is your form on an the worksheet or is it a UserForm
a. If it's a userform then we need to know the name of the textbox or combobox
2. What column is the account code located on the worksheet? Is it in a specific cell?
3. Where is the $ amount on the form? What is the cell location or textbox name?
4. Anything else you that may be pertinent.
If you wrap the code in code tags it helps with readability: The button that looks like this: </>
Thank you for your response. I will strip sensitive data out of both the main form, and the file we are pulling the data out of / want to verify data out of.
I will either upload them here, or upload them to a dropbox account with open access for editing.
Sincerely,
Brent Cochran
The above link will get you to the 2 files in question.
Hopefully this will help with finding a solution for what I'm looking for.
- I had to paste the MACRO on here as the file wouldn't upload. I can manual supply the files if needed. The above macro is pulling the totals for each of the account codes and totaling them. I need either an addition to this macro to can like I said invoice information and put a verification YES or NO in a certain cell, or a separate macro to add onto after this one.
