Macro similar to Sumifs function to update columns

[jayped]

Hi there,

I would like help creating a macro that can perform a task similar to sumifs function.

I have a workbook with multiple tabs. The first tab is the "Main" tab and there are three tabs following labelled "USD", "EUR" and "GBP".

I want to update the three currency tabs by using the name of the tab as reference, i.e. looking up "USD" in the main tab and summing.

Using the USD tab as my illustration; this is how the sheet is set up:

ID	Purchase	Refund	Purchase Fee	Refund Fee	Net Amount
100					=Purchase+Refund+Fees
101
102

The "Main" tab contains information for each column above. For ID 100, there are multiple line items and the currencies vary. I would therefore like a macro that will look for ID 100 in column E of Main tab, "Purchase" in column J, the tab name which in this case is "USD" in column M and calculate the sum based on these criteria using the amounts in column N.

For the refund column it will be similar to the above but instead of looking up "Purchase" in column J I need to look up "Refund" for every ID 100 that is in USD.

For the Purchase Fee and Refund Fee columns, it gets a bit more complicated. I need to sum columns O, P, Q, R & S in "Main" tab where "Purchase" is contained in column J, "USD" in column M and ID 100 in column E. And similarly for the Refund Fee column instead of "Purchase" I need to look up "Refund".

The Net Amount is a summation of the four preceding columns.

Can this be done in VBA?

 

[Rijnsent]

Hi Jayped,
yes, that sounds very possible with VBA, here a small bit of code to get you started. The main things you need are Loops and If-Then statements. The code below is far from perfect, but this sounds like an easy project to start with VBA. Learn more on VBA with e.g. Excel VBA Programming - a free course for complete beginners or Free VBA Training Course Check out the bits on Conditions and Loops. If you want to use this code: copy-paste it into a module. After that, you can run it completely pressing F5, but it's probably wiser to do it step-by-step, pressing F8 to execute the code line by line.
Cheers,
Koen

Sub ProcessData()

Set ShtMain = Worksheets("Main")
Set ShtUSD = Worksheets("USD")
'Comments - data Main:
'E : ID
'J : transaction type: Purchase / Refund / etc.
'M : Currency

'Loop through the ShtUSD from row 2 to LastRw
LastRw = 10

For Rw = 2 To LastRw
    CheckID = ShtUSD.Range("A" & Rw).Value
    'Set Purchase and Refund to 0
    ShtUSD.Range("B" & Rw).Value = 0
    ShtUSD.Range("C" & Rw).Value = 0
    
    'For every ID, loop through the Main sheet records - row 2 to 50 ?
    For Rw2 = 2 To 50
       If ShtMain.Range("E" & Rw2).Value = CheckID Then
          'Match found, add to the total
          If ShtMain.Range("M" & Rw2).Value = "USD" Then
             If ShtMain.Range("J" & Rw2).Value = "Purchase" Then
                ShtUSD.Range("B" & Rw).Value = ShtUSD.Range("B" & Rw).Value + htMain.Range("N" & Rw2).Value
             ElseIf ShtMain.Range("J" & Rw2).Value = "Refund" Then
                ShtUSD.Range("C" & Rw).Value = ShtUSD.Range("C" & Rw).Value + htMain.Range("N" & Rw2).Value
             Else
                'Unknown type
             End If
          End If
       End If
    Next Rw2
Next Rw

End Sub

 

[jayped]

This was quite close to perfect. Thank you!