-
This often happens, after I post, it's then that my brain fog starts to clear.
Anyway I got it figured out
Hope I didn't take up much of anyones time. And thanks
[VBA]
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
Dim i As Integer
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).Activate
i = MsgBox("Click YES to change: " & Found.Value & " to: BRICE TOOL" & _
" Click NO if you do wish to make any change, or Cancel to exit the procedure", _
vbQuestion + vbYesNoCancel, "The Name BRICE was found.")
Select Case i
Case vbYes
Found.EntireRow.Cells(3).Value = "BRICE TOOL"
Case vbNo
Found.EntireRow.Cells(3).Activate
GoTo ExitNow
Case vbCancel
Found.EntireRow.Cells(3).Activate
GoTo ExitNow
End Select
Else
Found.EntireRow.Cells(3).Activate
i = MsgBox("Click YES to change: " & Found.Value & " to: BRICE SEAT" & _
" Click NO if you do wish to make any change, or Cancel to exit the procedure", _
vbQuestion + vbYesNoCancel, "The Name BRICE was found.")
Select Case i
Case vbYes
Found.EntireRow.Cells(3).Value = "BRICE TOOL"
Case vbNo
Found.EntireRow.Cells(3).Activate
GoTo ExitNow
Case vbCancel
Found.EntireRow.Cells(3).Activate
GoTo ExitNow
End Select
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
Found.EntireRow.Cells(5).Activate
Else
MsgBox "No Match Found"
End If
Else
MsgBox "Please select a row greater than 15"
End If
ExitNow:
End Sub
[/VBA]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules