Hi I've created a spreadsheet to record VAT returns for a small computer repair business in Ireland. I like to tweak it to make it run smoother and faster - it has a selection of if statements working well but the macros are proving too difficult to implement.
There are two applicable VAT rates, 23% and 13.5%. Lets say I buy a new screen for a laptop, both VAT rates apply to the transaction. 23% for the screen and 13.5% for the labour for fitting the screen. "Device service with new parts" is an option in a drop down list. If I choose that option, I have to manually add a second row, one row for the labour and one for the parts
<tbody>
</tbody>
I have this macro that works but I want it to run any time I choose "device service with new parts" from the dropdown list. I would also like it to add the same value to the row in the "No:" column to the new row. In the above example row four should actually be row 3 - there should be two row 3s because both rows apply to the same transaction (the "No:" column refers to a transaction ID in a POS system) I hope Ive explained it well enough...here's the macro
Private Sub Workbook_Open()
'Updateby20150203
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.Columns(1)
xLastRow = WorkRng.Rows.Count
Application.ScreenUpdating = False
For xRowIndex = xLastRow To 1 Step -1
Set Rng = WorkRng.Range("A" & xRowIndex)
If Rng.Value = "Device Service with new parts" Then
Rng.Offset(1, 0).EntireRow.Insert Shift:=xlDown
End If
Next
Application.ScreenUpdating = True
End Sub
Ive have come close to getting it to work automatically but I get syntax errors every time. I don't know much about code, hopefully someone here can help me out.
Regards
John-Paul
There are two applicable VAT rates, 23% and 13.5%. Lets say I buy a new screen for a laptop, both VAT rates apply to the transaction. 23% for the screen and 13.5% for the labour for fitting the screen. "Device service with new parts" is an option in a drop down list. If I choose that option, I have to manually add a second row, one row for the labour and one for the parts
| No: | Amount | Description | VAT Rate | 13.50% | 23% |
| 1 | 30.0 | Device Service | low | 3.57 | 0.00 |
| 2 | 30.0 | Device Service | low | 3.57 | 0.00 |
| 3 | 50.0 | Device Service with new parts | low | 5.95 | 0.00 |
| 4 | 50.0 | new parts | High | 0.00 | 9.35 |
<tbody>
</tbody>
I have this macro that works but I want it to run any time I choose "device service with new parts" from the dropdown list. I would also like it to add the same value to the row in the "No:" column to the new row. In the above example row four should actually be row 3 - there should be two row 3s because both rows apply to the same transaction (the "No:" column refers to a transaction ID in a POS system) I hope Ive explained it well enough...here's the macro
Private Sub Workbook_Open()
'Updateby20150203
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.Columns(1)
xLastRow = WorkRng.Rows.Count
Application.ScreenUpdating = False
For xRowIndex = xLastRow To 1 Step -1
Set Rng = WorkRng.Range("A" & xRowIndex)
If Rng.Value = "Device Service with new parts" Then
Rng.Offset(1, 0).EntireRow.Insert Shift:=xlDown
End If
Next
Application.ScreenUpdating = True
End Sub
Ive have come close to getting it to work automatically but I get syntax errors every time. I don't know much about code, hopefully someone here can help me out.
Regards
John-Paul
