Hi Folks,
First, disclaimer. I'm not good with VBA at all but I can generally Google the beejeebers out of what I'm trying to do and eventually piece it together from info on forums like this. That's where the bulk of the following comes from but now I'm pretty well stuck.
I have a userform on sheet "Macros" with two text boxes and a button.
The userform is Delete_Transaction_Userform
The text boxes are Truck_TextBox and Stop_TextBox.
Clicking the button runs the following code which should take the input from Truck_Textbox and search column J in my second sheet, "Pick Input". When it finds a match it should compare the input in Stop_Textbox to the contents of the cell in column K. If both inputs match, the (always empty) row below and the row containing the match should be deleted, and the search should continue for another match.
Example:
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
If I run the macro with input 1 and 1, it should delete the first two rows. I get an object required error but it does delete those rows first. If I were to run it with input 1 and 2, I get the message "Transaction not Found", then "Transaction Removed", but nothing actually gets deleted.
I'm out of my depth, any thoughts?
First, disclaimer. I'm not good with VBA at all but I can generally Google the beejeebers out of what I'm trying to do and eventually piece it together from info on forums like this. That's where the bulk of the following comes from but now I'm pretty well stuck.
I have a userform on sheet "Macros" with two text boxes and a button.
The userform is Delete_Transaction_Userform
The text boxes are Truck_TextBox and Stop_TextBox.
Clicking the button runs the following code which should take the input from Truck_Textbox and search column J in my second sheet, "Pick Input". When it finds a match it should compare the input in Stop_Textbox to the contents of the cell in column K. If both inputs match, the (always empty) row below and the row containing the match should be deleted, and the search should continue for another match.
Code:
Option Explicit
Sub Remove_Transaction()
Dim TruckSearch As String
Dim StopSearch As String
Dim aCell As Range
On Error GoTo Err
Application.ScreenUpdating = False
Worksheets("Pick Input").Activate
'Get the values to search for from the userform text boxes
TruckSearch = Delete_Transaction_Userform.Truck_TextBox.Value
StopSearch = Delete_Transaction_Userform.Stop_TextBox.Value
'Find a match for the Truck textbox in column J
Set aCell = Columns(10).Find(What:=TruckSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'If a match is found then confirm the cell in column K also matches the input Stop textbox
If Not aCell Is Nothing And aCell.Offset(0, 1) = StopSearch Then
Do
'Delete the row found and the one below it until no more matches are found
aCell.Offset(1, 0).EntireRow.Delete
Rows(aCell.Row).Delete
Loop While Not aCell Is Nothing And aCell.Offset(0, 1) = StopSearch
Else 'If not found
MsgBox "Transaction not Found"
End If
'When all matches are deleted
MsgBox "Transaction Removed"
Worksheets("Macro").Activate
Application.ScreenUpdating = True
Exit Sub
Err:
MsgBox Err.Description
End Sub
Example:
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
If I run the macro with input 1 and 1, it should delete the first two rows. I get an object required error but it does delete those rows first. If I were to run it with input 1 and 2, I get the message "Transaction not Found", then "Transaction Removed", but nothing actually gets deleted.
I'm out of my depth, any thoughts?
Last edited: