rimkim
09-14-2019, 01:15 PM
This is a bit complicated to explain and I'm not well versed in VBA so please bear with me. The idea is to take a vertical list of transactions and build up an aging crosstable going down the list. Please refer to the 'desired output' tab of the attachment to follow along. Each blank header column, (F onwards) represents an incremental aging unit (eg. months). There will be as many columns as the Reset Counter value.
Based on the value in the 'Type' and 'Reset Counter' columns, I need to apply different logic. I have the first two pieces figured in the code. If Reset Counter is 1 then the Running Total goes into Column F; otherwise if the Type is Advance, then the values in the row above (cols F onwards) get pasted into the current row, shifted 1 column (G) and the Amt goes into current row column F.
The tricky bit is when the Type = Netting. One has to compare the current row Amt to the last value in the row above and offset it. I think we'll need to use arrays here. Defining NetAry as the range (previous row, columns F to F + RC-2).
If the abs(Amt) < abs(Amount of last element in NetAry) then update last element of Net Ary = Amt + Last element value. Then paste the array in the current row starting in column G; similar to advance. In my code, the array is getting updated but not pasting.
The hardest part of this is when the abs(Amt)> then the abs(last element of the array). You have to sequentially wipe out element by element, netting until you have a balance. And then paste that on the row below.
Ex. amt is -10. Array from previous row has (5,0,10,2). Then, the -10 should wipe out the 2 leaving 2 in position 3. The updated array will be (5,0,2,0).
Ex. amt is -1. Then updated array will be (5,0,10,1)
Ex. amt is -14. Then updated array will be (3,0,0,0)
It is crucial to retain the position of the elements so that when transposed to the current row, col G. The new aging will be correctly reflected.
All this must be done row by row. Sorry if my explanation is not clear. Please let me know if any questions. I would appreciate any help as this is a head scratcher!
Based on the value in the 'Type' and 'Reset Counter' columns, I need to apply different logic. I have the first two pieces figured in the code. If Reset Counter is 1 then the Running Total goes into Column F; otherwise if the Type is Advance, then the values in the row above (cols F onwards) get pasted into the current row, shifted 1 column (G) and the Amt goes into current row column F.
The tricky bit is when the Type = Netting. One has to compare the current row Amt to the last value in the row above and offset it. I think we'll need to use arrays here. Defining NetAry as the range (previous row, columns F to F + RC-2).
If the abs(Amt) < abs(Amount of last element in NetAry) then update last element of Net Ary = Amt + Last element value. Then paste the array in the current row starting in column G; similar to advance. In my code, the array is getting updated but not pasting.
The hardest part of this is when the abs(Amt)> then the abs(last element of the array). You have to sequentially wipe out element by element, netting until you have a balance. And then paste that on the row below.
Ex. amt is -10. Array from previous row has (5,0,10,2). Then, the -10 should wipe out the 2 leaving 2 in position 3. The updated array will be (5,0,2,0).
Ex. amt is -1. Then updated array will be (5,0,10,1)
Ex. amt is -14. Then updated array will be (3,0,0,0)
It is crucial to retain the position of the elements so that when transposed to the current row, col G. The new aging will be correctly reflected.
All this must be done row by row. Sorry if my explanation is not clear. Please let me know if any questions. I would appreciate any help as this is a head scratcher!