VBA in excel
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
VBA in excel
Hello sir, I hope you are doing well.
I need your support for VBA code. In this code for column i (subtotal) i need a value of column H (Total).
But the thing is all columns are based on column A (invoice no.) If any invoice no. is repeated then i want the subtotal of that invoices also.
ex:
column A Column H Column I
101 $40 $40
102 $50 $80
102 $30 $80
103 $20 $20
102 in column A if repeated then subtotal 50+30=80 in shown in column I that is getting from column H.
Output sample sheet attached that i need by VBA code
In Data sheet Data & VBA Code is available.
Thank you for your support and attention.
With regards
Prince
I need your support for VBA code. In this code for column i (subtotal) i need a value of column H (Total).
But the thing is all columns are based on column A (invoice no.) If any invoice no. is repeated then i want the subtotal of that invoices also.
ex:
column A Column H Column I
101 $40 $40
102 $50 $80
102 $30 $80
103 $20 $20
102 in column A if repeated then subtotal 50+30=80 in shown in column I that is getting from column H.
Output sample sheet attached that i need by VBA code
In Data sheet Data & VBA Code is available.
Thank you for your support and attention.
With regards
Prince
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA in excel
There is no need to use VBA for this - it can easily be done with formulas.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: VBA in excel
Dear Sir,
Everything is functioning flawlessly, but I find myself in need of a VBA code. The reason for this request is that I receive 10-15 sheets on a daily basis, and manually applying a formula to each sheet has become a challenging task. I would greatly appreciate your assistance in creating a VBA code for this purpose.
Thank you and best regards
Prince
Everything is functioning flawlessly, but I find myself in need of a VBA code. The reason for this request is that I receive 10-15 sheets on a daily basis, and manually applying a formula to each sheet has become a challenging task. I would greatly appreciate your assistance in creating a VBA code for this purpose.
Thank you and best regards
Prince
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA in excel
Here you go.
Code: Select all
Sub CreateWorkingSheet()
Dim wsSource As Worksheet
Dim wsWorking As Worksheet
Application.ScreenUpdating = False
' Set references to the source and working sheets
Set wsSource = ThisWorkbook.Sheets("Delivery Details")
On Error Resume Next
Set wsWorking = ThisWorkbook.Sheets("Working Sheet")
On Error GoTo 0
' Check if Working Sheet exists, create it if not
If wsWorking Is Nothing Then
Set wsWorking = ThisWorkbook.Sheets.Add
wsWorking.Name = "Working Sheet"
Else
wsWorking.Cells.Clear
End If
' Copy columns A to G from source to working sheet
wsSource.Range("A:G").Copy wsWorking.Range("A1")
' Add column headers H to O
wsWorking.Range("H1:O1").Value = Array("TOTAL", "SUBTOTAL", _
"PAID", "FB#", "SCAC", "PROBIL NO", "INV DATE", "NOTES")
' Find the last row in the working sheet
LastRow = wsWorking.Cells(Rows.Count, 1).End(xlUp).Row
' Formulas
wsWorking.Range("H2").Resize(LastRow - 1).Formula = "=E2+G2"
wsWorking.Range("I2").Resize(LastRow - 1).Formula = _
"=SUMIFS(H$2:H$" & LastRow & ",A$2:A$" & LastRow & ",A2)"
wsWorking.Range("H2:I" & LastRow).NumberFormat = _
wsWorking.Range("E2").NumberFormat
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: VBA in excel
Thank you so much sir for your support. It's working perfectly. Hats off to you sir.
With regards
Prince
With regards
Prince
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: VBA in excel
Mr. Hans I need one more edition in this code. In 1st column A (Invoice no.) it can be available some blank cells also.
In this case rest of the data is not showing on the new sheet (Working sheet). I want all data from sheet A to B.
Thank you for your attention.
With regards
Prince
In this case rest of the data is not showing on the new sheet (Working sheet). I want all data from sheet A to B.
Thank you for your attention.
With regards
Prince
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA in excel
Change the line
to
Code: Select all
LastRow = wsWorking.Cells(Rows.Count, 1).End(xlUp).Row
Code: Select all
LastRow = wsWorking.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: VBA in excel
Thank you so much sir for your support and time.
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: VBA in excel
sir, there is one error in this code. when column A has no value. then subtotal showing 0.
kindly help to sort out this error.
Is this macro code compatible with office 365 also?
With regards
Prince
kindly help to sort out this error.
Is this macro code compatible with office 365 also?
With regards
Prince
You do not have the required permissions to view the files attached to this post.
Last edited by prince on 17 Oct 2023, 14:14, edited 1 time in total.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA in excel
If it should indeed be $187.00, change the lines
to
Code: Select all
wsWorking.Range("I2").Resize(LastRow - 1).Formula = _
"=SUMIFS(H$2:H$" & LastRow & ",A$2:A$" & LastRow & ",A2)"
Code: Select all
wsWorking.Range("I2").Resize(LastRow - 1).Formula = _
"=SUMIFS(H$2:H$" & LastRow & ",A$2:A$" & LastRow & ",IF(A2="""",""="",A2))"
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: VBA in excel
If the invoice number is empty then the subtotal should remain the same as the total column. It should not be PLUS.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA in excel
In that case, use
Code: Select all
wsWorking.Range("I2").Resize(LastRow - 1).Formula = _
"=IF(A2="""",H2,SUMIFS(H$2:H$" & LastRow & ",A$2:A$" & LastRow & ",A2))"
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: VBA in excel
Yes, It's working perfectly as per my requirements.
Thank you so much sir for your valuable time & support.
with warm regards
Prince
Thank you so much sir for your valuable time & support.
with warm regards
Prince