frank_m
06-06-2012, 06:37 PM
Procedure Finds the Next Customer name containing the word "Brice" and changes it to either "Brice Tool" or "Brice Seat", depending on PO# format
Rule: "BRICE TOOL" PO#'s always contain either dashes or periods or RGB or some of each and BRICE SEAT never contains any of those.
As my code is now it makes the change when it finds and checks the criteria of the match. What I need is to give the user the option of making the change or not.
Edit: A better description is I need a yes/no/cancel msgbox
- Yes = make change -- then allow the next click of the button to find the next match.
- No = do not make change -- but allow the next click of the button to find the next match
--Cancel -- Exit the Procedure
Sample Data - Also a workbook attached with the macro.
27684 BRICE 05/14/12 04-26-12 should be BRICE TOOL
27625 BRICE TOOL 05/08/12 04.26.12
27052 BRICE TOOL 02/16/12 02.02RGB
27017 BRICE 02/13/12 02.02 RGB should be BRICE TOOL
26372 BRICE 11/18/11 P074119 should be BRICE SEAT
26114 BRICE TOOL 10/21/11 10.20.RGB
23023 BRICE 09/02/10 067992 should be BRICE SEAT
Private Sub CommandButton1_Click()
'Use PO# format to change BRICE to BRICE TOOL or BRICE SEAT
Dim StrtAdrss As String
Dim Found As Range
Dim stringToFind
Dim Rng As Range
Dim LastRow As Long
stringToFind = "BRICE"
StrtAdrss = ActiveCell.Address
With ActiveSheet
LastRow = .Cells.SpecialCells(xlCellTypeVisible).Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Set Rng = .Range(.Cells(16, 1), .Cells(LastRow, 5)).SpecialCells(xlCellTypeVisible)
End With
If ActiveCell.Row > 15 Then
ActiveCell.EntireRow.Cells(3).Activate
Set Found = Rng.Find(What:=stringToFind, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Found Is Nothing Then
' match was found
If InStr(Found.EntireRow.Cells(5).Value, ".") >= 1 _
Or InStr(Found.EntireRow.Cells(5).Value, "-") >= 1 _
Or InStr(Found.EntireRow.Cells(5).Value, "RGB") >= 1 Then
Found.EntireRow.Cells(3).Value = "BRICE TOOL"
Else
Found.EntireRow.Cells(3).Value = "BRICE SEAT"
End If
Do Until Found.Row > 15 And StrtAdrss <> Found.Address
Set Found = Rng.FindNext(Found)
Loop
ActiveWindow.ScrollRow = Found.Row - 5'allows me to view 5 rows above
Found.EntireRow.Cells(5).Activate
Else
MsgBox "No Match Found"
End If
Else
MsgBox "Please select a row greater than 15"
End If
End Sub
Rule: "BRICE TOOL" PO#'s always contain either dashes or periods or RGB or some of each and BRICE SEAT never contains any of those.
As my code is now it makes the change when it finds and checks the criteria of the match. What I need is to give the user the option of making the change or not.
Edit: A better description is I need a yes/no/cancel msgbox
- Yes = make change -- then allow the next click of the button to find the next match.
- No = do not make change -- but allow the next click of the button to find the next match
--Cancel -- Exit the Procedure
Sample Data - Also a workbook attached with the macro.
27684 BRICE 05/14/12 04-26-12 should be BRICE TOOL
27625 BRICE TOOL 05/08/12 04.26.12
27052 BRICE TOOL 02/16/12 02.02RGB
27017 BRICE 02/13/12 02.02 RGB should be BRICE TOOL
26372 BRICE 11/18/11 P074119 should be BRICE SEAT
26114 BRICE TOOL 10/21/11 10.20.RGB
23023 BRICE 09/02/10 067992 should be BRICE SEAT
Private Sub CommandButton1_Click()
'Use PO# format to change BRICE to BRICE TOOL or BRICE SEAT
Dim StrtAdrss As String
Dim Found As Range
Dim stringToFind
Dim Rng As Range
Dim LastRow As Long
stringToFind = "BRICE"
StrtAdrss = ActiveCell.Address
With ActiveSheet
LastRow = .Cells.SpecialCells(xlCellTypeVisible).Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Set Rng = .Range(.Cells(16, 1), .Cells(LastRow, 5)).SpecialCells(xlCellTypeVisible)
End With
If ActiveCell.Row > 15 Then
ActiveCell.EntireRow.Cells(3).Activate
Set Found = Rng.Find(What:=stringToFind, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Found Is Nothing Then
' match was found
If InStr(Found.EntireRow.Cells(5).Value, ".") >= 1 _
Or InStr(Found.EntireRow.Cells(5).Value, "-") >= 1 _
Or InStr(Found.EntireRow.Cells(5).Value, "RGB") >= 1 Then
Found.EntireRow.Cells(3).Value = "BRICE TOOL"
Else
Found.EntireRow.Cells(3).Value = "BRICE SEAT"
End If
Do Until Found.Row > 15 And StrtAdrss <> Found.Address
Set Found = Rng.FindNext(Found)
Loop
ActiveWindow.ScrollRow = Found.Row - 5'allows me to view 5 rows above
Found.EntireRow.Cells(5).Activate
Else
MsgBox "No Match Found"
End If
Else
MsgBox "Please select a row greater than 15"
End If
End Sub