Adding an extra row a the bottom of a table below total

[Paulo H]

Hi,
I have created a table for quotes which I have also added a total row at the bottom. Sometimes I may want to add a discount and want to do this by adding another row below the total row and show the discount.
I have created a macro that adds this to the table but this can only be done before I populate the table. I want to be able to add this Row as an option at the end after the quote/table is complete

Has anyone any ideas of vba code To do this?

Thanks

 

[cortexnotion]

Hi @Paulo H, try this:

Sub Disc()

Dim DiscVal As Long, LR As Long
LR = ThisWorkbook.Sheets("yoursheetname").Range("A" & Rows.Count).End(xlUp).Row +1
DiscVal = Application.InputBox(prompt:="Enter the Discount percentage amount", Title:="Discount", Default:="Enter discount amount here", Type:=1)

With ThisWorkbook.Sheets("yoursheetname").Range("A" & LR)
      .Value =  .Offset(-1, 0) * DiscVal
End With

 

[cortexnotion]

Sorry made a mistake there it should be:

Sub Disc()

Dim DiscVal As Long, LR As Long
LR = ThisWorkbook.Sheets("yoursheetname").Range("A" & Rows.Count).End(xlUp).Row +1
DiscVal = Application.InputBox(prompt:="Enter the Discount percentage amount", Title:="Discount", Default:="Enter discount amount here", Type:=1)

With ThisWorkbook.Sheets("yoursheetname").Range("A" & LR)
      .Value =  (.Offset(-1, 0) / 100) * DiscVal
End With

End Sub

 

[Paulo H]

Sorry made a mistake there it should be:

Sub Disc()

Dim DiscVal As Long, LR As Long
LR = ThisWorkbook.Sheets("yoursheetname").Range("A" & Rows.Count).End(xlUp).Row +1
DiscVal = Application.InputBox(prompt:="Enter the Discount percentage amount", Title:="Discount", Default:="Enter discount amount here", Type:=1)

With ThisWorkbook.Sheets("yoursheetname").Range("A" & LR)
      .Value =  (.Offset(-1, 0) / 100) * DiscVal
End With

End Sub

 

[Paulo H]

Hi tried this but it comes up with error 13 type mismatch

Thanks for your help so far

 

[cortexnotion]

Hi @Paulo H, have you changed the references to match your worksheet?

 

[Paulo H]

Hi @Paulo H, have you changed the references to match your worksheet?

 

[Paulo H]

Dim DiscVal As Long, LR As Long
LR = ThisWorkbook.Sheets("Quote Form").Range("A" & Rows.Count).End(xlUp).Row + 1
DiscVal = Application.InputBox(prompt:="Enter the Discount percentage amount", Title:="Discount", Default:="Enter discount amount here", Type:=1)

With ThisWorkbook.Sheets("Quote Form").Range("A" & LR)
.Value = (.Offset(-1, 0) / 100) * DiscVal
End With

does this help? the debug highlights the after With

 

[cortexnotion]

Is A the column you’re working on?
Best to post your data so we can see what you’re working with

 

[Paulo H]

Is A the column you’re working on?
Best to post your data so we can see what you’re working with

'

Hi this workrks as I want via a Macro but only adds at row 20. this is ok it I a pply at start of quote but if I want to add later it adds at row 20. I want to add at the end. BTW I already use the Totals row to it needs to go after that one

Many thanks
' Discount10 Macro
' 10%
'
' Keyboard Shortcut: Ctrl+d
'
Range("A20:G20").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = 6299648
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("F20").Select
Selection.NumberFormat = "$#,##0.00"
Range("E20").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D20").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D20").Select
ActiveCell.FormulaR1C1 = "Special Discounted Price"
Range("E20").Select
ActiveCell.FormulaR1C1 = "Total"
Range("F20").Select
End Sub

Private Sub Discount25_Click()
Range("A20:G20").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = 6299648
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("F20").Select
Selection.NumberFormat = "$#,##0.00"
Range("E20").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D20").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D20").Select
ActiveCell.FormulaR1C1 = "Special Discounted Price"
Range("E20").Select
ActiveCell.FormulaR1C1 = "Total"
Range("F20").Select
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("F20").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C/1.05)"
Range("E20").Select
ActiveCell.FormulaR1C1 = "Total"
Range("D20").Select
ActiveCell.FormulaR1C1 = "Special Discounted Price"
Range("F20").Select

End Sub