ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   finding (https://www.excelbanter.com/excel-programming/367649-finding.html)

enyaw
finding
 
I have two textboxes on a form. I want to be able to use textbox1 to search
column A and textbox2 to search columnB. I need to be able to search and
find the two numbers but the numbers have to be in the same row. Anyone got
any ideas?

Bernie Deitrick
finding
 
Assign this to a commandbutton on your userform: works with string or numbers....

Private Sub CommandButton1_Click()
Dim c As Range
Dim strF1 As String
Dim strF2 As String
Dim strAdd As String

strF1 = UserForm1.TextBox1.Text
strF2 = UserForm1.TextBox2.Text

With ActiveSheet.Range("A:A")
Set c = .Find(strF1, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
strAdd = c.Address
If c(1, 2).Value = strF2 Then GoTo Notify
Else:
MsgBox "Not Found"
Exit Sub
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < strAdd Then
Do
If c(1, 2).Value = strF2 Then GoTo Notify
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < strAdd
End If
End With

Notify:
MsgBox """" & strF1 & """ is next to """ & _
strF2 & """ in cells " & c.Resize(1, 2).Address

End Sub

HTH,
Bernie
MS Excel MVP


"enyaw" wrote in message
...
I have two textboxes on a form. I want to be able to use textbox1 to search
column A and textbox2 to search columnB. I need to be able to search and
find the two numbers but the numbers have to be in the same row. Anyone got
any ideas?



enyaw
finding
 
Private Sub CommandButton1_Click()
If TextBox5 = "" Then
MsgBox "Please enter Part Number"
UserForm3.TextBox5.SetFocus
ElseIf TextBox6 = "" Then
MsgBox "Please enter Sequence Number"
UserForm3.TextBox6.SetFocus
Else
Set rnga = range("B:B")

Set a = rnga.Find(UserForm3.TextBox5, LookIn:=xlValues, LookAt:=xlWhole)
If Not a Is Nothing Then
MsgBox "Number found"




UserForm3.TextBox5 = ""
UserForm3.Hide

UserForm1.OptionButton1 = False
UserForm1.OptionButton2 = False
UserForm1.TextBox5.Enabled = True
UserForm1.TextBox6.Enabled = True
UserForm1.TextBox7.Enabled = True
UserForm1.TextBox8.Enabled = True
UserForm1.OptionButton3 = False
UserForm1.OptionButton4 = False
UserForm1.TextBox9.Enabled = True
UserForm1.TextBox10.Enabled = True
UserForm1.TextBox11.Enabled = True

UserForm1.OptionButton5 = False
UserForm1.OptionButton6 = False
UserForm1.TextBox12.Enabled = True
UserForm1.TextBox13.Enabled = True
UserForm1.TextBox14.Enabled = True

UserForm1.OptionButton7 = False
UserForm1.OptionButton8 = False
UserForm1.TextBox15.Enabled = True
UserForm1.TextBox16.Enabled = True
UserForm1.TextBox17.Enabled = True

UserForm1.TextBox2.Enabled = False
UserForm1.TextBox1.SetFocus
UserForm1.TextBox1 = Cells(a.Row, 1)
Cells(a.Row, 1).ClearContents
UserForm1.TextBox2 = Cells(a.Row, 2)
Cells(a.Row, 2).ClearContents
UserForm1.TextBox3 = Cells(a.Row, 3)
Cells(a.Row, 3).ClearContents
UserForm1.TextBox4 = Cells(a.Row, 4)
Cells(a.Row, 4).ClearContents
UserForm1.TextBox5 = Cells(a.Row, 5)
Cells(a.Row, 5).ClearContents
UserForm1.TextBox6 = Cells(a.Row, 6)
Cells(a.Row, 6).ClearContents
UserForm1.TextBox7 = Cells(a.Row, 7)
Cells(a.Row, 7).ClearContents
UserForm1.TextBox8 = Cells(a.Row, 8)
Cells(a.Row, 8).ClearContents
UserForm1.TextBox9 = Cells(a.Row, 9)
Cells(a.Row, 9).ClearContents
UserForm1.TextBox10 = Cells(a.Row, 10)
Cells(a.Row, 10).ClearContents
UserForm1.TextBox11 = Cells(a.Row, 11)
Cells(a.Row, 11).ClearContents
UserForm1.TextBox12 = Cells(a.Row, 12)
Cells(a.Row, 12).ClearContents
UserForm1.TextBox13 = Cells(a.Row, 13)
Cells(a.Row, 13).ClearContents
UserForm1.TextBox14 = Cells(a.Row, 14)
Cells(a.Row, 14).ClearContents
UserForm1.TextBox15 = Cells(a.Row, 15)
Cells(a.Row, 15).ClearContents
UserForm1.TextBox16 = Cells(a.Row, 16)
Cells(a.Row, 16).ClearContents
UserForm1.TextBox17 = Cells(a.Row, 17)
Cells(a.Row, 17).ClearContents
UserForm1.TextBox18 = Cells(a.Row, 19)
Cells(a.Row, 19).ClearContents
UserForm1.TextBox19 = Cells(a.Row, 18)
Cells(a.Row, 18).ClearContents
Columns("A:s").Select
Selection.Sort Key1:=range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
range("A2").Select


Else
MsgBox "No match found"
UserForm3.TextBox5 = ""
UserForm3.TextBox5.SetFocus
UserForm3.TextBox5 = ""
End If

End If
End Sub

This is the code I have so far. I want to modify if to use two textboxes
and find two numbers in seperate columns on the same row and return the
values to another form.

"Bernie Deitrick" wrote:

Assign this to a commandbutton on your userform: works with string or numbers....

Private Sub CommandButton1_Click()
Dim c As Range
Dim strF1 As String
Dim strF2 As String
Dim strAdd As String

strF1 = UserForm1.TextBox1.Text
strF2 = UserForm1.TextBox2.Text

With ActiveSheet.Range("A:A")
Set c = .Find(strF1, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
strAdd = c.Address
If c(1, 2).Value = strF2 Then GoTo Notify
Else:
MsgBox "Not Found"
Exit Sub
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < strAdd Then
Do
If c(1, 2).Value = strF2 Then GoTo Notify
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < strAdd
End If
End With

Notify:
MsgBox """" & strF1 & """ is next to """ & _
strF2 & """ in cells " & c.Resize(1, 2).Address

End Sub

HTH,
Bernie
MS Excel MVP


"enyaw" wrote in message
...
I have two textboxes on a form. I want to be able to use textbox1 to search
column A and textbox2 to search columnB. I need to be able to search and
find the two numbers but the numbers have to be in the same row. Anyone got
any ideas?




Bernie Deitrick
finding
 
Try just the finding part:

Private Sub CommandButton1_Click()
Dim c As Range
Dim strF1 As String
Dim strF2 As String
Dim strAdd As String

If UserForm3.TextBox5.Text = "" Then
MsgBox "Please enter Part Number"
UserForm3.TextBox5.SetFocus
Exit Sub
End If

If UserForm3.TextBox6.Text = "" Then
MsgBox "Please enter Sequence Number"
UserForm3.TextBox6.SetFocus
Exit Sub
End If

strF1 = UserForm3.TextBox5.Text
strF2 = UserForm3.TextBox6.Text

'Assumes that Part numbers are in column B
With ActiveSheet.Range("B:B")
Set c = .Find(strF1, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
strAdd = c.Address
If c(1, 2).Value = strF2 Then GoTo Notify
Else:
MsgBox "Not Found"
Exit Sub
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < strAdd Then
Do
If c(1, 2).Value = strF2 Then GoTo Notify
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < strAdd
End If
End With

Notify:
MsgBox """" & strF1 & """ is next to """ & _
strF2 & """ in cells " & c.Resize(1, 2).Address

End Sub


--
HTH,
Bernie
MS Excel MVP


"enyaw" wrote in message
...
Private Sub CommandButton1_Click()
If TextBox5 = "" Then
MsgBox "Please enter Part Number"
UserForm3.TextBox5.SetFocus
ElseIf TextBox6 = "" Then
MsgBox "Please enter Sequence Number"
UserForm3.TextBox6.SetFocus
Else
Set rnga = range("B:B")

Set a = rnga.Find(UserForm3.TextBox5, LookIn:=xlValues, LookAt:=xlWhole)
If Not a Is Nothing Then
MsgBox "Number found"




UserForm3.TextBox5 = ""
UserForm3.Hide

UserForm1.OptionButton1 = False
UserForm1.OptionButton2 = False
UserForm1.TextBox5.Enabled = True
UserForm1.TextBox6.Enabled = True
UserForm1.TextBox7.Enabled = True
UserForm1.TextBox8.Enabled = True
UserForm1.OptionButton3 = False
UserForm1.OptionButton4 = False
UserForm1.TextBox9.Enabled = True
UserForm1.TextBox10.Enabled = True
UserForm1.TextBox11.Enabled = True

UserForm1.OptionButton5 = False
UserForm1.OptionButton6 = False
UserForm1.TextBox12.Enabled = True
UserForm1.TextBox13.Enabled = True
UserForm1.TextBox14.Enabled = True

UserForm1.OptionButton7 = False
UserForm1.OptionButton8 = False
UserForm1.TextBox15.Enabled = True
UserForm1.TextBox16.Enabled = True
UserForm1.TextBox17.Enabled = True

UserForm1.TextBox2.Enabled = False
UserForm1.TextBox1.SetFocus
UserForm1.TextBox1 = Cells(a.Row, 1)
Cells(a.Row, 1).ClearContents
UserForm1.TextBox2 = Cells(a.Row, 2)
Cells(a.Row, 2).ClearContents
UserForm1.TextBox3 = Cells(a.Row, 3)
Cells(a.Row, 3).ClearContents
UserForm1.TextBox4 = Cells(a.Row, 4)
Cells(a.Row, 4).ClearContents
UserForm1.TextBox5 = Cells(a.Row, 5)
Cells(a.Row, 5).ClearContents
UserForm1.TextBox6 = Cells(a.Row, 6)
Cells(a.Row, 6).ClearContents
UserForm1.TextBox7 = Cells(a.Row, 7)
Cells(a.Row, 7).ClearContents
UserForm1.TextBox8 = Cells(a.Row, 8)
Cells(a.Row, 8).ClearContents
UserForm1.TextBox9 = Cells(a.Row, 9)
Cells(a.Row, 9).ClearContents
UserForm1.TextBox10 = Cells(a.Row, 10)
Cells(a.Row, 10).ClearContents
UserForm1.TextBox11 = Cells(a.Row, 11)
Cells(a.Row, 11).ClearContents
UserForm1.TextBox12 = Cells(a.Row, 12)
Cells(a.Row, 12).ClearContents
UserForm1.TextBox13 = Cells(a.Row, 13)
Cells(a.Row, 13).ClearContents
UserForm1.TextBox14 = Cells(a.Row, 14)
Cells(a.Row, 14).ClearContents
UserForm1.TextBox15 = Cells(a.Row, 15)
Cells(a.Row, 15).ClearContents
UserForm1.TextBox16 = Cells(a.Row, 16)
Cells(a.Row, 16).ClearContents
UserForm1.TextBox17 = Cells(a.Row, 17)
Cells(a.Row, 17).ClearContents
UserForm1.TextBox18 = Cells(a.Row, 19)
Cells(a.Row, 19).ClearContents
UserForm1.TextBox19 = Cells(a.Row, 18)
Cells(a.Row, 18).ClearContents
Columns("A:s").Select
Selection.Sort Key1:=range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
range("A2").Select


Else
MsgBox "No match found"
UserForm3.TextBox5 = ""
UserForm3.TextBox5.SetFocus
UserForm3.TextBox5 = ""
End If

End If
End Sub

This is the code I have so far. I want to modify if to use two textboxes
and find two numbers in seperate columns on the same row and return the
values to another form.

"Bernie Deitrick" wrote:

Assign this to a commandbutton on your userform: works with string or numbers....

Private Sub CommandButton1_Click()
Dim c As Range
Dim strF1 As String
Dim strF2 As String
Dim strAdd As String

strF1 = UserForm1.TextBox1.Text
strF2 = UserForm1.TextBox2.Text

With ActiveSheet.Range("A:A")
Set c = .Find(strF1, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
strAdd = c.Address
If c(1, 2).Value = strF2 Then GoTo Notify
Else:
MsgBox "Not Found"
Exit Sub
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < strAdd Then
Do
If c(1, 2).Value = strF2 Then GoTo Notify
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < strAdd
End If
End With

Notify:
MsgBox """" & strF1 & """ is next to """ & _
strF2 & """ in cells " & c.Resize(1, 2).Address

End Sub

HTH,
Bernie
MS Excel MVP


"enyaw" wrote in message
...
I have two textboxes on a form. I want to be able to use textbox1 to search
column A and textbox2 to search columnB. I need to be able to search and
find the two numbers but the numbers have to be in the same row. Anyone got
any ideas?


All times are GMT +1. The time now is 06:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com