macro or VBA code "is it possible?"

[joeny0706]

This will be tricky. Well for me it is if it is even possible. I get a report of invoices each week. I need to upload these into my accounting software. The problem is the report I get has the products shown at the sold price. So when a discount is applied it is shown at the sold price. While I need them to go into accounting software at full price then I need to add a new line between each invoice that shows the discount dollar amount. The pic shows the excel sheet. Column C is the invoice number. F is the discounted price and I will create a macro to add the normal price in column G. So I need to group each invoice number. enter a blank line under each different invoice Then find out what percentage the item was discounted at. The find what the total discount amount so that I can enter that as a new item in a blank row under the group of invoices. Then when I upload to my accounting software I can upload each item at normal price but include a item that will be the total discount amount in dollars. And on the line with the new discount item also add the same invoice number and customer number as the line above that

Does anyone think that is possible. I will be receiving these report weekly and would love to be able automate the input into QuickBooks. have already worked for hour creating 10 other macros to make nessacry changes to excel sheets and just found out today that I can not upload at the discounted price. I need to upload at the normal price then create a new item with the discounted dollar amount to add to the invoice.

[Paul_Hossler]

Probably, but create a sample workbook with the current lines and what you want the new lines to looks like

Show any calculations

[joeny0706]

I created everything on row 4 and 11. I inserted both thoserows. I also manually entered the normal price for each item on column G. Firstneed to group all the invoice rows together and insert a blank line betweenthem. Then copied the invoice number and customer name from the line above inthe blank row. In column D it needs to add “20 Sales & Discount”. Column Fis the items all at the discounted price. I will need to figure out the savingsfor each item on each invoice and add them all tighter then enter that amountin column F of the created row. So the total of all the discounts needs to showon the invoice as its own item with the total discount savings.
I need to multiply the quality by the item price to get the total price then ad al item totals toghetr and do same with full price and enter difference on new created row in column F

I will not need the normal price on the excel sheet. Eitherway is fine. When I upload the data it will auto add the normal price for each item.

[Paul_Hossler]

And the workbook?

No one wants to type all that from a screen shot in order to test

See note #2 in my signature for details

[joeny0706]

Thanks


I manually typed everything in row 4, 8, and 12 also column G added. column Gis the normal price for those products. The final outcome I will need is a newrow under the group of items from one invoice group that has the “20 Sales& Discount” in column d and the final savings amount in F. Then copy thedate invoice number and account name from the row above that.

[joeny0706]

I already have ten macros that I need to run on the data I receive.The attached file is how I receive that data.


Also attached is the outcome I have after I run all my macros.
All macros I use on the received data are also attached

[joeny0706]

attached are all the items at the normal price.

[Paul_Hossler]

Thanks


I manually typed everything in row 4, 8, and 12 also column G added. column Gis the normal price for those products. The final outcome I will need is a newrow under the group of items from one invoice group that has the “20 Sales& Discount” in column d and the final savings amount in F. Then copy thedate invoice number and account name from the row above that.

1. Is your F12 value correct?

Capture.JPG

2. Try this

Option Explicit


Sub AddLines()
    Dim wsInput As Worksheet
    Dim rData As Range, rData1 As Range, rLast As Range
    Dim iRow As Long
    Dim dDiscount As Double

    Application.ScreenUpdating = False

    'set data
    Set wsInput = Worksheets("Input")        '   <<<<< Change WS name
    Set rLast = wsInput.Cells(1, wsInput.Columns.Count).End(xlToLeft)
    Set rData = Range(wsInput.Cells(1, 1), rLast).EntireColumn
    Set rData = Intersect(rData, wsInput.Cells(1, 1).CurrentRegion.EntireRow)
    Set rData1 = rData.Cells(2, 1).Resize(rData.Rows.Count - 1, rData.Columns.Count)
    
    'sort by invoice data and invoice number
    With wsInput.Sort
        .SortFields.Clear
        .SortFields.Add Key:=rData1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add2 Key:=rData1.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange rData
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'go up and add "20 Sales & Discount" to col D after invoice change
    With wsInput
        For iRow = rData.Rows.Count To 2 Step -1
            If .Cells(iRow + 1, 2).Value <> .Cells(iRow, 2).Value Then
                .Rows(iRow + 1).Insert
                .Cells(iRow + 1, 4).Value = "20 Sales & Discount"
            End If
        Next iRow
    End With

    'go down and calc discount and fill in data
    dDiscount = 0#
    With wsInput
        Set rData = .Cells(1, 1).CurrentRegion
        For iRow = 2 To rData.Rows.Count
            If Len(.Cells(iRow, 1).Value) > 0 Then
                dDiscount = dDiscount + .Cells(iRow, 5).Value * (.Cells(iRow, 7).Value - .Cells(iRow, 6).Value)
            
            Else
                .Cells(iRow, 1).Value = .Cells(iRow - 1, 1).Value
                .Cells(iRow, 2).Value = .Cells(iRow - 1, 2).Value
                .Cells(iRow, 3).Value = .Cells(iRow - 1, 3).Value
                .Cells(iRow, 6).Value = dDiscount
                dDiscount = 0#
            End If
        Next iRow
    End With

    'cleanup
    Application.ScreenUpdating = True

End Sub

[joeny0706]

Thank you very much for working with and helping me. It is very close. Doing everything I need but it is calculating the total sale amount for the items. I need only the total savings.
Yes F12 was wrong
I the picture below it shows what the output was after I ran your macro.
It also shows what the values need to be for the final output
Ps
Most of the items are $3 but some are different. They areall listed in the excel file items prices. . I am not sure how but am willing to help. If it would help I could probably create a macro that addsthe normal price in column G. Would take me awhile but I could if that wouldhelp?

The file “after macros” is the one I would be running yourmacro on.


Again thanks for helping me with this.

[joeny0706]

I just added all the normal prices to column G then ran it.It does work correctly like that. So there needs to be a macro that adds thenormal sale price to column G then run your macro.
That is great. I can create one using the same one I used toadd the class. I can try Monday but if I have problems I might be asking you.

Thank you. This makes me very happy. I also am going to tryand learn how your macro works once I am done with this all.

[Paul_Hossler]

Play with this

It opens the Items WB, remembers Normal prices, and hen close the WB

Option Explicit
Sub AddLines()
    Dim wbItem As Workbook
    Dim wsInput As Worksheet
    Dim rData As Range, rData1 As Range, rLast As Range, rTemp As Range
    Dim iRow As Long, iItem As Long
    Dim dDiscount As Double
    Dim vItems As Variant, vPrices As Variant

    Application.ScreenUpdating = False

    'get normal prices
    Workbooks.Add "C:\Users\Daddy\Downloads\item prices.xlsx"   '   <<<<<<<<<<<<< change WB path
    Set wbItem = ActiveWorkbook
    
    Set rTemp = wbItem.Worksheets("Sheet1").Range("C1")
    Set rTemp = Range(rTemp, rTemp.End(xlDown))
    vItems = Application.WorksheetFunction.Transpose(rTemp)
    Set rTemp = wbItem.Worksheets("Sheet1").Range("E1")
    Set rTemp = Range(rTemp, rTemp.End(xlDown))
    vPrices = Application.WorksheetFunction.Transpose(rTemp)
    
    wbItem.Close False


    'set data
    Set wsInput = Worksheets("Input")        '   <<<<< Change WS name
    Set rLast = wsInput.Cells(1, wsInput.Columns.Count).End(xlToLeft)
    Set rData = Range(wsInput.Cells(1, 1), rLast).EntireColumn
    Set rData = Intersect(rData, wsInput.Cells(1, 1).CurrentRegion.EntireRow)
    Set rData1 = rData.Cells(2, 1).Resize(rData.Rows.Count - 1, rData.Columns.Count)
    

    'add Normal Prices
    With wsInput
        .Cells(1, 7).Value = "Normal"
        For iRow = 2 To rData.Rows.Count
            iItem = 0
            On Error Resume Next
            iItem = Application.WorksheetFunction.Match(.Cells(iRow, 4).Value, vItems, 0)
            On Error GoTo 0
            
            If iItem > 0 Then .Cells(iRow, 7).Value = vPrices(iItem)
        Next iRow
    End With
    
    
    'sort by invoice data and invoice number
    With wsInput.Sort
        .SortFields.Clear
        .SortFields.Add Key:=rData1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add2 Key:=rData1.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange rData
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    'go up and add "20 Sales & Discount" to col D after invoice change
    With wsInput
        For iRow = rData.Rows.Count To 2 Step -1
            If .Cells(iRow + 1, 2).Value <> .Cells(iRow, 2).Value Then
                .Rows(iRow + 1).Insert
                .Cells(iRow + 1, 4).Value = "20 Sales & Discount"
            End If
        Next iRow
    End With


    'go down and calc discount and fill in data
    dDiscount = 0#
    With wsInput
        Set rData = .Cells(1, 1).CurrentRegion
        For iRow = 2 To rData.Rows.Count
            If Len(.Cells(iRow, 1).Value) > 0 Then
                dDiscount = dDiscount + .Cells(iRow, 5).Value * (.Cells(iRow, 7).Value - .Cells(iRow, 6).Value)
            
            Else
                .Cells(iRow, 1).Value = .Cells(iRow - 1, 1).Value
                .Cells(iRow, 2).Value = .Cells(iRow - 1, 2).Value
                .Cells(iRow, 3).Value = .Cells(iRow - 1, 3).Value
                .Cells(iRow, 6).Value = dDiscount
                dDiscount = 0#
            End If
        Next iRow
    End With


    'cleanup
    Application.ScreenUpdating = True
End Sub

[joeny0706]

It is working perfect. But while uploading to the accounting software I did come across one issue. I need to have the discount amount put in column H. It needs to be input a different way then the prices. Only way is to have the discount in column H.

I will try to figure out how to change it but might need help. I will let you know Thanks

Thank a lot for all the hep

[joeny0706]

I changed “.Cells(iRow, 6).Value = dDiscount” to “.Cells(iRow,8).Value = dDiscount”
And seems to be working. Again thanks for all the help

[macropod]

Cross-posted at: https://www.techsupportforum.com/for...e-1233620.html

Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3