The formula exrpessed in  a VB code, if possible

Hi David,

When posting a macro enabled workbook please don't password protect the VBA modules.  The first thing I do when I download an xlsm is open it with macros disabled, then check the code to see if there's anything malicious in there.

As I can't do this with your workbook I have removed it as I can't vouch for the VBA code.

I'm not saying there was anything malicious in there, but I need to check and make sure.

Please re-upload the file so we can check the code.

Thx

Phil

MsgBox Application.WorksheetFunction.VLookup("search value", Range("A1:B100"), 2, 0)

hope this is what you want

Sorry, file decrypt for your reference.

In short  I hope a formula can vlookup the net bal of the code ( in teh dabase worksheet] if Code matched  in the Report sheet.  

Any chance to set this formula ?

Hi David

You should give us the actual expected result as your report amount does not tally with the AR Database.

Why don't you just use a Pivot Table to summarize the AR Database instead of using formulas (or is it VBA that you want)?

Sunny

Because we must use the format designated by Headoffice, pivot table format NOT accepted

So what we want is to insert the Code Net balance from the AR Database to the Report Layout, but using Vlookup we can only map one balance into the Report.

Preferably we can using VBA to automate the mapping process ie insert the net balance of each Code from AR dabase to fit into the Report layout.  

Can you attach your actual expected result? VERY IMPORTANT

Which columns are you matching to get the net balance?

Is your attachment the actual format? It has merged cells and may cause some difficulties. I also notice your values have both USD and YEN.

Sorry for so many questions, need to be clear about what you want

Sunny

Hi David

Give this a try. To prevent double/triple summing, I am matching the code in each cell of column B against the cell above.

If it is the same (duplicate), it will not sum. This will only work if the codes in column B are sorted.

Hope this helps.

Sunny

Thank Sunny wil give it a try, to see if work .. 

After a bit modificaion to incorporate the formula you set up, finally did work out well in the Report template, our deep thanks to you Sunny again.

Hi David

You are welcome. Glad to know you are able to figure it out.

Cheers

Sunny

I think many agreed in the Forum, your methodology to tackle the problem is compresensive enough to Excel users...

Make life a lot easier ...