Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > finding
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 108
Default 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?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default 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?


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 108
Default 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?



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default 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?





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding a Value SueK Excel Discussion (Misc queries) 3 September 21st 08 07:27 PM
Finding The MAX Value carl Excel Worksheet Functions 6 July 25th 07 09:36 PM
Finding Same Row Value JN[_6_] Excel Programming 4 May 26th 06 04:43 PM
finding the last value Simon Shaw Excel Discussion (Misc queries) 8 January 16th 05 10:02 AM
Finding a sum Realwoodies Excel Programming 3 February 25th 04 11:46 PM


All times are GMT +1. The time now is 03:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement