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?
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
What is the error & what line is highlighted?
 

Fluff

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

ADVERTISEMENT

In that case you need to add a Next at the end of the loop.
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
In that case you need to add a Next at the end of the loop.
Okay have added, however now they give me a run time error on
VBA Code:
lastrow = Worksheets("sheet1").Cells(rows.Count, 5).End(x1Up).Row
 

Fluff

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

ADVERTISEMENT

It should be xlUp (lower case L) not x1Up (number one)
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
It should be xlUp (lower case L) not x1Up (number one)
okay nice catch :0 but now i changed my code to 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, 5).End(xlUp).Row

For i = 2 To lastrow

If Worksheets("sheet1").Cells(i, 1).Value = rx Or (InStr(Me.TextBox1.Value, "/") > 0 Or InStr(Me.TextBox1.Value, ",") > 0) Then
m = MsgBox("Please confirm", vbYesNo, "Double Check")

    End If
 Next
End Sub

is it possible to help me stop from getting an endless loop?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
That's not an endless loop, it will stop when it gets to the last used cell in col E
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
That's not an endless loop, it will stop when it gets to the last used cell in col E
oh but the msgbox keep popping out when there is only 2 cell with "/" in it
 

Watch MrExcel Video

Forum statistics

Threads
1,114,208
Messages
5,546,549
Members
410,746
Latest member
Tcestnick
Top