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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,053
Office Version
  1. 365
Platform
  1. Windows
It will appear whenever the criteria is met, because that is what you have told it to do. ;)
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,053
Office Version
  1. 365
Platform
  1. Windows
Nope, your checking if col A equals the value in the textbox, or the textbox contains either / or ,
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,053
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can the text box contain either / or ,
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,053
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case test for that before you run the loop.
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,053
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,892
Messages
5,544,893
Members
410,643
Latest member
sng
Top