Check VBA code to locate value in cell

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello this is the current code I have

VBA Code:
Private Sub TextBox1_AfterUpdate()

Dim m As String
Dim rx As String
rx = Trim(TextBox1.Text)
lastrow = Worksheets("sheet1").Cells(rows.Count, 5).End(x1Up).Row

For i = 2 To lastrow
If Worksheets("sheet1").Cells(i, 1).Value = rx Then
TextBox2.Text = Worksheets("sheet1").Cells(i, 2).Value

If rx = (InStr(Me.TextBox1.Value, "/") > 0 Or InStr(Me.TextBox1.Value, ",") > 0) Then
m = MsgBox("Please confirm", vbYesNo, "Double Check")
        End If
    End If
End Sub

I want it such that if there is a "/" or "," along with the found value. A message box should pop out. Currently, there seems to be a error. Could you help me edit it?
 
It will appear whenever the criteria is met, because that is what you have told it to do. ;)
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
It will appear whenever the criteria is met, because that is what you have told it to do. ;)
hmm, fluff just to check with you, I am asking it to pop a msg box when it find 1 "/" or "," in a cell of column E right?
Or repeatedly
 
Upvote 0
Nope, your checking if col A equals the value in the textbox, or the textbox contains either / or ,
 
Upvote 0
Nope, your checking if col A equals the value in the textbox, or the textbox contains either / or ,
ohh, could you help me change it to if column A contain either / or , a message box will pop out.
while ensuring col A have to equal to the value in the textbox first
 
Upvote 0
Can the text box contain either / or ,
 
Upvote 0
In that case test for that before you run the loop.
 
Upvote 0
ohh kk let me do the changes
is it like this?
VBA Code:
Private Sub TextBox1_AfterUpdate()


Dim m As String
Dim rx As String
rx = Trim(TextBox1.Text)
lastrow = Worksheets("sheet1").Cells(rows.Count, 1).End(xlUp).Row + (InStr(Me.TextBox1.Value, "/") > 0 Or InStr(Me.TextBox1.Value, ",") > 0)
For i = 2 To lastrow

If Worksheets("sheet1").Cells(i, 5).Value = rx Or (0) Then
m = MsgBox("Please confirm", vbYesNo, "Double Check")

    End If
 Next
End Sub
 
Upvote 0
More like
VBA Code:
Private Sub TextBox1_AfterUpdate()


Dim m As String
Dim rx As String
rx = Trim(TextBox1.Text)
LastRow = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
   If InStr(Me.TextBox1.Value, "/") > 0 Or InStr(Me.TextBox1.Value, ",") > 0 Then
      If MsgBox("Please confirm", vbYesNo, "Double Check") = vbNo Then Exit Sub
   End If
      
For i = 2 To LastRow

If Worksheets("sheet1").Cells(i, 5).Value = rx Then
m = MsgBox("Please confirm", vbYesNo, "Double Check")

    End If
 Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top