PDA

View Full Version : [SOLVED] macro or VBA code "is it possible?"



joeny0706
12-06-2018, 01:10 PM
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
12-06-2018, 01:15 PM
Probably, but create a sample workbook with the current lines and what you want the new lines to looks like

Show any calculations

joeny0706
12-06-2018, 01:32 PM
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
12-06-2018, 05:34 PM
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
12-07-2018, 06:26 AM
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
12-07-2018, 06:45 AM
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
12-07-2018, 08:29 AM
attached are all the items at the normal price.

Paul_Hossler
12-07-2018, 07:32 PM
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?

23370

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
12-07-2018, 09:14 PM
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
12-07-2018, 09:34 PM
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
12-08-2018, 07:17 AM
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
12-09-2018, 11:10 AM
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
12-09-2018, 11:19 AM
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
12-09-2018, 05:14 PM
Cross-posted at: https://www.techsupportforum.com/forums/f57/excel-macros-if-possible-1233620.html

Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3