Inserting rows with built in logic

[msmithdynamicsgp]

I have a flat file that I am preparing for upload and am having trouble with the macro to finish it off. Perhaps you guys could help.

I have included a sample file that I hope is super clear compared to my written explanation below. I have a tab named input (source) and output (what I am hoping to achieve after running the macro).

What I need to accomplish is the following:

Starting with row 2 (right after header)....

Insert two rows new rows that are an exact copy of the prior row with the exception of two specific changes.

1) I put a different constant in the account number column for each new row.
2) Place 2% of Debit amount in Credit column for one of the two new rows
3) Place 98% of the Debit amount in Credit column for the other new row.

Loop through this for each row in the flat file starting at two. My sample file is only a few rows, but I could easily have hundreds in a file with live data.

Thanks in advance for any insight you can provide,

-Michael

[nimrod]

Public Sub DupeAndMorphRows()

  ' Config
  VendorCol = 1
  FirstCol = 1
  LastCol = 14
  AccCol = 8
  DebCol = 10
  CrdCol = 11
  
  ' Verify input sheet is active
  msg = "Before running input sheet must be activeSheet !"
  resp = MsgBox(msg, vbExclamation + vbOKCancel, "Confirm Input Sheet Active")
  If Not resp = vbOK Then Exit Sub
  
  ' Determine bottom row based on Vendor
  BotRow = Cells(Cells(1, VendorCol).EntireColumn.Cells.Count, VendorCol).End(xlUp).Row
  
  ' make copy of input sheet
  ActiveSheet.Copy Before:=Sheets(1)
  
  For CurrRow = BotRow To 2 Step -1
     
     ' Dupe Rows
     Rows(CurrRow & ":" & CurrRow).Copy
     Rows(CurrRow & ":" & CurrRow + 1).Insert Shift:=xlDown
     OrigDebValue = Cells(CurrRow, DebCol).Value
     
     For NewRow = 1 To 3
        
        ' Determine Values base on Row
        Select Case NewRow
           Case 1
              accVal = "5300-FAT1"
              debVal = OrigDebValue
              crdVal = 0
           Case 2
              accVal = "5320"
              debVal = 0
              crdVal = OrigDebValue * 0.02
           Case 3
              accVal = "2100"
              debVal = 0
              crdVal = OrigDebValue * 0.98
        End Select
           
           'Morph Values
           TargRow = CurrRow - 1 + NewRow
           Cells(TargRow, AccCol).Value = accVal
           Cells(TargRow, DebCol).Value = debVal
           Cells(TargRow, CrdCol).Value = crdVal
           
     Next NewRow
  Next CurrRow
   
   ' Clean up & format
   Application.CutCopyMode = False
   Columns("K:K").NumberFormat = "$#,##0.00"
   Columns("A:B").Delete
End Sub

[Leith Ross]

Hello Michael,

I feel a little more information is needed before any workable solutions can be proposed, unless your live data matches 100% with the example. Based on standard accounting practices for numbering the chart of accounts, it appears that the credit is being applied to a liability account and a possible inventory or COGS account. It is unlikely a 2% credit will be applied to the same two accounts in all cases.

You should put together a sample workbook using the live data. Change any data that is confidential or sensitive in nature. This will ensure you receive a workable solution with minimal revision.

[msmithdynamicsgp]

nimrod,

I appreciate you taking the time to put the code together for me! It worked flawlessly the first time I tried it, producing the expected result. This is a major time saver for me and allows me to deploy a process much sooner than expected.

I'll certainly do what I can to learn from the code above and put similar ideas into practice on other projects going forward.

Leith,

You make a good point and seem to have good understanding of accounting practices. You guessed right in that all 53?? account are COGS. Similarly, the other is a liability.

Believe it or not, it is actually true that every invoice that this macro will see will always get a 2% discount and they will always be applied to the same two accounts. The terms are technically identical for this vendor as well. Price and quantity vary. The reason I ended up with so many columns of data is that our integration utility is finicky.

I appreciate the help and all suggestions.

-Michael

[Leith Ross]

Hello Micheal,

Thanks for responding and clarifying my questions about the data. I am glad I didn't bet on being right.

If the answer you have received has solved your problem, please mark this post as solved.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[msmithdynamicsgp]

Solved! Thanks everyone.