Using an automated method to validate Finance reports against ERP reports

[Gunasekaran]

Dear Sir,

I am working on a VBA project, so I am wondering if it is possible to validate both Report Grant Total Values automatically. As a new member of this team, I need your help. I would appreciate your advice.

Steps are mentioned in that attachment File Data.

In the Finance Report, I must enter the Sub Total values based on ERP Report MEP Codes. If there is no MEP code available in the Finance Report. "NA" needs to be updated

Is it possible to do this validation in macro????

 

[BobBridges]

Your instructions inside the workbook read:

1) I have to validate manually for Each MEP Code Grand Total Value vs Finance Report
2) In Original Finance Report, Rows are dynamic, but last Row before Row should be like "INCOME_EXPENSE - Net Income or (Loss)"
3) I have Enter Trial Balance Sum Total Values and Validation Formula in Finance Report to get the Result
4) Based on ERP Report MEP Code wise, I have to entered that Sub Total values in Finance Report. If no MEP Code avaiable in Finance Report. Update "NA"

Is it Possible this Validation in Macro ????

I think you mean that you have two numbers, on two separate worksheets, that you want to compare to be sure they're equal. But what numbers? I think you should tell us explicitly how the two numbers are calculated.

(Yes, a macro can probably do it. You just haven't told us how you do it manually.)

 

[Gunasekaran]

Hi Sir, I mentioned Detailed my Simple Task PRocess Steps in this attachment. Kindly let me know if any question.

A quick note on my challenge, how to memorize each MEP code, and then finds it in the finance report in one by one,

Expecting - Validate by MEP Code Total Vs. INCOME_EXPENSE - Net Income or (Loss) in Finance Report

 

[BobBridges]

The new instructions are better, but there are parts I still don't understand. The first step is to copy 'ERP Report'!K:L to a new worksheet for processing; I get that. The second step finds data in a pivot table—but I never use pivot tables and don't know much about them. (Really I know almost nothing about them.) Where is that table?

We'll do this a step at a time.

 

[Gunasekaran]

Hi Sir,

I will handle the first two steps and then proceed to the pivot table. As a result of these row labels, the MEP code values will be updated in the Finance Report.

The MEP code can be found in Rows 8 of the Finance Report, followed by finding the MEP code. The last two rows after updating this MEP code value are in the same column.

In the Finance Report, Sir, you will find all the information you need

then Final Calculate by MEP Code Total Vs INCOME_EXPENSE - Net Income or (Loss) in Finance Report

 

[BobBridges]

I'll paraphrase: Tell me if I understand this right:

1) Copy 'ERP Report'!K:L to a new worksheet.
2) Create a table in columns E and F. Each row contains one of the MEP codes in col E, and the sum of all the balances for that MEP code in col F.

Is that about right? If so, next question: I understood you to say that you were working on a VBA program to do this: How far have you gotten with it, so far? Can you show us what you've written? Then I can offer corrections.

 

[Gunasekaran]

Hi Sir, Thanks for your question and support, after multiple try and attempted, now I got that automatic solution via VBA... Thanks once again

 

[vletm]

Gunasekaran
Next code gives Your row 300 sums without Pivot-table to row 7 ( then no need to scroll so much ).
Was that ... what are You looking for?

Sub Do_It()
    Application.ScreenUpdating = False
    FS = "Finance Report"
    ER = "ERP Report"
    With Sheets(ER)
        y = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    End With
    With Sheets(FS)
        x = 3
        Do
            .Cells(7, x) = WorksheetFunction.SumIfs(Sheets(ER).Range("K1:K" & y), Sheets(ER).Range("L1:L" & y), .Cells(8, x))
            x = x + 1
        Loop Until .Cells(8, x) = Empty
        .Cells(7, "A").Activate
    End With
    Application.ScreenUpdating = True
End Sub

 

[Gunasekaran]

Hi Sir, this is my Hard code. may be little big code, if possible can u edit and give short term of code

>>> use code - tags <<<

On Error Resume Next

ThisWorkbook.Worksheets("Temp").Delete

On Error GoTo 0

Sheets.Add(Before:=Sheets("BPC Master")).Name = "Temp"


Set Tem = ThisWorkbook.Worksheets("Temp")

ThisWorkbook.Worksheets("Segment TB workings").Select

ThisWorkbook.Worksheets("Segment TB workings").Range("K:L").Copy

Tem.Activate

Sheets("Temp").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

crr = "B1:B" & Cells(Rows.Count, "B").End(xlUp).Row
Range(crr) = Evaluate("IF(" & crr & "="""","""",TRIM(" & crr & "))")


lr = Tem.Range("E" & Rows.Count).End(xlUp).Row - 1

Set WR = ThisWorkbook.Sheets("BPC Master")

lrr = WR.Range("B" & Rows.Count).End(xlUp).Row

lgr = lrr - 5

For i = 2 To lr

Tem.Activate

loop_ctr = ActiveSheet.Range("E" & i).Value
loop_crr = ActiveSheet.Range("F" & i).Value
   

Set Rng = WR.Rows(8).Find(What:=loop_ctr)
   WR.Select
Rng.Select

MMG = WR.Range("B" & Rows.Count).End(xlUp).Row + 3

MMG = MMG - 8

Rng.Offset(MMG, 0).Value = loop_crr

Rng.Offset(MMG, 0).Offset(2, 0).Select

ActiveCell.FormulaR1C1 = "=R[-2]C+R[-6]C"

Next
   
Range("B10000").End(xlUp).Offset(3, 0).Value = "TB Balance"
   
Range("B10000").End(xlUp).Interior.Color = 5296274

   
Range("B10000").End(xlUp).Offset(2, 0).Value = "TB Vs BPC Validation"
   
Range("B10000").End(xlUp).Interior.Color = 5296274
   
End Sub

 

[Gunasekaran]

Hi Sir, Really Great Code,

But I would like to update this ERP Total Value for, after two rows of last row in Finance Report instead of 7th Rows.

Because then only I can put validation Formula . Both are possible in your code. kindly help me.

 

[Gunasekaran]

i added Last row function, now its working.. thanks

>>> use code - tags <<<

Sub Do_It()
    Application.ScreenUpdating = False
    FS = "Finance Report"
    ER = "ERP Report"
    With Sheets(ER)
        y = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    End With
    With Sheets(FS)
'     >>> mod
        lr = .Range("B" & Rows.Count).End(xlUp).Row + 2
'    <<< mod
        x = 3
        Do
            .Cells(lr, x) = WorksheetFunction.SumIfs(Sheets(ER).Range("K1:K" & y), Sheets(ER).Range("L1:L" & y), .Cells(8, x))
            x = x + 1
        Loop Until .Cells(8, x) = Empty
        .Cells(lr, "A").Activate
    End With
    Application.ScreenUpdating = True
End Sub

 

[Gunasekaran]

Hi Sir, Now i need that last row validation formula in your same code. please

 

[vletm]

What formula?
What else?
Why those should be in the bottom? ... why not in the top?
You should able to give expected details at once ... many times so called 'minor changes' would mean a lot of extra work for others.

 

[Gunasekaran]

Really apologize for this. Now i need this validation formula attached in this excel file. in Finance Report sheet.


automatically while ERP report value updated time, this “Income_Expense - Net Income or (Less)” column + above row column value is added..

 

[vletm]

Gunasekaran
Did You mean that for Your file ... ?
I used file - which has Your previous need too.
... and You missed two why-questions.

 

[Gunasekaran]

Hi Sir, thanks a lot for your quick support and help. It's Fine for me.. also really sorry for the inconvience for repeated questions...