Insert column before specific column to multiply qty*price

A

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
606
Office Version
  1. 2019
Hi
I would insert BALANCE column after QTY column E and multiply PRICE * QTY
exclude the headers from multiply PRICE * QTY.
KashfMabiatReportALMUFAWADIEA.xls
ABCDEFG
1TOTALDATEINVOICE NOPRICEQTYCODEBRAND
22,000.0002024.04.28835500.00041483KM 235/55R17 HS52 KOR
3TOTALDATEINVOICE NOPRICEQTYCODEBRAND
44,310.0002024.05.21954465.00041535TOP TRUST 7.5-16-8PR F2-1 TT CHINA
5490.00051300XPRO 70A L KOR
6TOTALDATEINVOICE NOPRICEQTYCODEBRAND
71,020.0002024.05.21957510.00021538DONGA 70A L KOR
8TOTALDATEINVOICE NOPRICEQTYCODEBRAND
93,000.0002024.06.041002750.00041346BS 255/70R15C D840
10TOTALDATEINVOICE NOPRICEQTYCODEBRAND
11345.0002024.06.101020345.00011550VIPIEMME 45A L IT
12TOTALDATEINVOICE NOPRICEQTYCODEBRAND
1312,200.0002024.06.131029550.00041535TOP TRUST 7.5-16-8PR F2-1 TT CHINA
142,500.00041554TOP TRUST 16.9-30 12PR R-1 TR218 TT CHI
15TOTALDATEINVOICE NOPRICEQTYCODEBRAND
162,265.0002024.07.091097485.00041535TOP TRUST 7.5-16-8PR F2-1 TT CHINA
17325.00011573DONGAH TUBE 16.9-3 TR218A VIT
18TOTALDATEINVOICE NOPRICEQTYCODEBRAND
192,050.0002024.07.1011031,025.00021568MOTORMECH 150A L KOR
20TOTALDATEINVOICE NOPRICEQTYCODEBRAND
214,270.0002024.07.30970000020425.00041343KM 215/65R16 TA21 KOR
221,285.00021495XPRO 200A L KOR
23TOTALDATEINVOICE NOPRICEQTYCODEBRAND
24390.0002024.08.05970000043390.00011337XPRO 55A L KOR
25TOTALDATEINVOICE NOPRICEQTYCODEBRAND
26390.0002024.08.11970000077390.00011337XPRO 55A L KOR
27TOTALDATEINVOICE NOPRICEQTYCODEBRAND
284,200.0002024.08.199700001422,100.00021285GC 1200R20 AZ0183 CHI
Page 0



what I want
KashfMabiatReportALMUFAWADIEA.xls
ABCDEFGH
1TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
22,000.0002024.04.28835500.00042,000.001483KM 235/55R17 HS52 KOR
3TOTALDATEINVOICE NOPRICEQTYCODEBRAND
44,310.0002024.05.21954465.00041,860.001535TOP TRUST 7.5-16-8PR F2-1 TT CHINA
5490.00052,450.001300XPRO 70A L KOR
6TOTALDATEINVOICE NOPRICEQTYCODEBRAND
71,020.0002024.05.21957510.00021,020.001538DONGA 70A L KOR
8TOTALDATEINVOICE NOPRICEQTYCODEBRAND
93,000.0002024.06.041002750.00043,000.001346BS 255/70R15C D840
10TOTALDATEINVOICE NOPRICEQTYCODEBRAND
11345.0002024.06.101020345.0001345.001550VIPIEMME 45A L IT
12TOTALDATEINVOICE NOPRICEQTYCODEBRAND
1312,200.0002024.06.131029550.00042,200.001535TOP TRUST 7.5-16-8PR F2-1 TT CHINA
142,500.000410,000.001554TOP TRUST 16.9-30 12PR R-1 TR218 TT CHI
15TOTALDATEINVOICE NOPRICEQTYCODEBRAND
162,265.0002024.07.091097485.00041,940.001535TOP TRUST 7.5-16-8PR F2-1 TT CHINA
17325.0001325.001573DONGAH TUBE 16.9-3 TR218A VIT
18TOTALDATEINVOICE NOPRICEQTYCODEBRAND
192,050.0002024.07.1011031,025.00022,050.001568MOTORMECH 150A L KOR
20TOTALDATEINVOICE NOPRICEQTYCODEBRAND
214,270.0002024.07.30970000020425.00041,700.001343KM 215/65R16 TA21 KOR
221,285.00022,570.001495XPRO 200A L KOR
23TOTALDATEINVOICE NOPRICEQTYCODEBRAND
24390.0002024.08.05970000043390.0001390.001337XPRO 55A L KOR
25TOTALDATEINVOICE NOPRICEQTYCODEBRAND
26390.0002024.08.11970000077390.0001390.001337XPRO 55A L KOR
27TOTALDATEINVOICE NOPRICEQTYCODEBRAND
284,200.0002024.08.199700001422,100.00024,200.001285GC 1200R20 AZ0183 CHI
Page 0
Cell Formulas
RangeFormula
F2,F28,F26,F24,F21:F22,F19,F16:F17,F13:F14,F11,F9,F7,F4:F5F2=D2*E2


this is what I try to do that
VBA Code: 
Sub inst()
Dim ws As Worksheet
Dim i As Long
For i = 1 To 2
Sheets(i).Range("F:F").EntireColumn.Insert
Sheets(i).Range("F:F") = "BALANCE"
Sheets(i).Range("F:F").FormulaR1C1 = "=RC[1]*RC[2]"
Sheets(i).Range("F:F").Value = Sheets(i).Range("F:F").Value
Next i

End Sub

no need show formulas, don't repeat insert column when every time run the macro and show numbers as formatting when insert column .

I have two sheet to implement that.
than
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
See if this does what you want.

VBA Code: 
Sub Balance()
  Columns("F").Insert
  With Range("F1").Resize(Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=iferror(RC[-2]*RC[-1],"""")"
    .NumberFormat = "#,##0.00"
    .Cells(1).Value = "BALANCE"
    .Columns.AutoFit
    .Value = .Value
  End With
End Sub
 
Upvote 0
Hi peter again,
I think you don't read this
don't repeat insert column when every time run the macro
Click to expand...
that what I got this
KashfMabiatReportALMUFAWADIEA.xls
ABCDEFGHIJKLMNOP
1TOTALDATEINVOICE NOPRICEQTYBALANCEBALANCEBALANCEBALANCEBALANCEBALANCEBALANCEBALANCEBALANCECODEBRAND
22,000.0002024.04.28835500.00042,000.002,000.002,000.002,000.002,000.002,000.002,000.002,000.002,000.001483KM 235/55R17 HS52 KOR
3TOTALDATEINVOICE NOPRICEQTYCODEBRAND
44,310.0002024.05.21954465.00041,860.001,860.001,860.001,860.001,860.001,860.001,860.001,860.001,860.001535TOP TRUST 7.5-16-8PR F2-1 TT CHINA
5490.00052,450.002,450.002,450.002,450.002,450.002,450.002,450.002,450.002,450.001300XPRO 70A L KOR
6TOTALDATEINVOICE NOPRICEQTYCODEBRAND
71,020.0002024.05.21957510.00021,020.001,020.001,020.001,020.001,020.001,020.001,020.001,020.001,020.001538DONGA 70A L KOR
8TOTALDATEINVOICE NOPRICEQTYCODEBRAND
93,000.0002024.06.041002750.00043,000.003,000.003,000.003,000.003,000.003,000.003,000.003,000.003,000.001346BS 255/70R15C D840
10TOTALDATEINVOICE NOPRICEQTYCODEBRAND
11345.0002024.06.101020345.0001345.00345.00345.00345.00345.00345.00345.00345.00345.001550VIPIEMME 45A L IT
Page 0



also I would implement for specific sheets as I said
I have two sheet to implement that.
Click to expand...
sorry I didn't mentioned sheets names , but I try with this
Sub Balance()
Dim ws As Variant
For Each ws In Sheets(Array("page 0", "page 1"))
With ws
.Columns("F").Insert
With .Range("F1").Resize(Range("A" & Rows.Count).End(xlUp).Row)
.FormulaR1C1 = "=iferror(RC[-2]*RC[-1],"""")"
.NumberFormat = "#,##0.00"
.Cells(1).Value = "BALANCE"
.Columns.AutoFit
.Value = .Value
End With
End With
Next ws
End Sub
the problem will repeat insert BALANCE column, should not to that
 
Upvote 0
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details

:huh: Why would you keep running the macro if you have already run it?

Anyway, try this version.
VBA Code: 
Sub balance_v2()
  Dim ws As Worksheet
  
  For Each ws In Sheets(Array("page 0", "page 1"))
    If ws.Range("F1").Value <> "BALANCE" Then
      ws.Columns("F").Insert
      With ws.Range("F1").Resize(ws.Range("A" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=iferror(RC[-2]*RC[-1],"""")"
        .NumberFormat = "#,##0.00"
        .Cells(1).Value = "BALANCE"
        .Columns.AutoFit
        .Value = .Value
      End With
    End If
  Next ws
End Sub
 
Upvote 0
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy.
Click to expand...
sorry I realized that after elapsed time 10 minutes and this is the limited time to edited. ;)
:huh: Why would you keep running the macro if you have already run it?
Click to expand...
this case could occurs unintentionally by user.
the code works greatly .(y)
many thanks for your assistance. :)
 
Upvote 0
just I would mod slightly ,please.
I would add BALANCE word for each row contains header like this
KashfMabiatReportALMUFAWADIEA.xls
ABCDEFGH
1TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
22,000.0002024.04.28835500.00042,000.001483KM 235/55R17 HS52 KOR
3TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
44,310.0002024.05.21954465.00041,860.001535TOP TRUST 7.5-16-8PR F2-1 TT CHINA
5490.00052,450.001300XPRO 70A L KOR
6TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
71,020.0002024.05.21957510.00021,020.001538DONGA 70A L KOR
8TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
93,000.0002024.06.041002750.00043,000.001346BS 255/70R15C D840
10TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
11345.0002024.06.101020345.0001345.001550VIPIEMME 45A L IT
12TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
1312,200.0002024.06.131029550.00042,200.001535TOP TRUST 7.5-16-8PR F2-1 TT CHINA
142,500.000410,000.001554TOP TRUST 16.9-30 12PR R-1 TR218 TT CHI
15TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
162,265.0002024.07.091097485.00041,940.001535TOP TRUST 7.5-16-8PR F2-1 TT CHINA
17325.0001325.001573DONGAH TUBE 16.9-3 TR218A VIT
18TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
192,050.0002024.07.1011031,025.00022,050.001568MOTORMECH 150A L KOR
20TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
214,270.0002024.07.30970000020425.00041,700.001343KM 215/65R16 TA21 KOR
221,285.00022,570.001495XPRO 200A L KOR
23TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
24390.0002024.08.05970000043390.0001390.001337XPRO 55A L KOR
25TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
26390.0002024.08.11970000077390.0001390.001337XPRO 55A L KOR
27TOTALDATEINVOICE NOPRICEQTYBALANCECODEBRAND
284,200.0002024.08.199700001422,100.00024,200.001285GC 1200R20 AZ0183 CHI
Page 0
 
Upvote 0
VBA Code: 
Sub Balance_v3()
  Dim ws As Worksheet
  
  For Each ws In Sheets(Array("page 0", "page 1"))
    If ws.Range("F1").Value <> "BALANCE" Then
      ws.Columns("F").Insert
      With ws.Range("F1").Resize(ws.Range("A" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=iferror(RC[-2]*RC[-1],""BALANCE"")"
        .NumberFormat = "#,##0.00"
        .Columns.AutoFit
        .Value = .Value
      End With
    End If
  Next ws
End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

A
  • Solved
Autonumbering in cell based on last row in column
Replies
2
Views
61
abdo meghari
A
A
  • Question Question
Using ADODB way t populate data under header from file to another
Replies
0
Views
59
abdo meghari
A
A
  • Question Question
populate & update invoice information across sheets based on invoice number
Replies
0
Views
325
abdo meghari
A
A
  • Solved
Add elements for adjacent cells by insert column based on multiple characters
2
Replies
17
Views
663
felixstraube
F

Forum statistics

Threads
1,223,610
Messages
6,173,339
Members
452,510
Latest member
RCan29

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top