FindMethod 2 col range

  • Thread starter Thread starter CG Rosén
  • Start date Start date
C

CG Rosén

Hi Group,

Have the code below. The names I´am looking for are in Range("E1:E50") and
could
appear at least two times in the range. To not only find the first name
selected in
Textbox1 I also need to check the values in Range("F1:F50") to find an
exclusive
value. The value to find in the later range is in ComboBox1.
Is it possible to do this with the Find method?

Brgds

CG Rosén

Dim z

With Sheets("constants").Range("E1:E50")
Set z = .Find(TextBox1.Text, LookIn:=xlValues, SearchOrder:=xlByColumns)

If z Is Nothing Then

code......

Else

code........

End If
End With
 
Dim z as Range, bFnd as Boolean
Dim fAddr as String

With Sheets("constants").Range("E1:E50")
Set z = .Find(TextBox1.Text, LookIn:=xlValues, SearchOrder:=xlByColumns)
bFnd = False
If z Is Nothing Then
msgbox Textbox1.Text & " is not found"

Else
if z.offset(0,1).Value = Combobox1.Value then
z.Select
bFnd = True
msgbox Textbox1.Text & " is found at " & z.address
else
fAddr = z.address
Do
set z =.FindNext(z)
if z.offset(0,1).Value = Combobox1.Value then
z.select
bFnd = True
msgbox Textbox1.Text & " is found at " & z.address
exit do
End if
Loop until z.address = fAddr
End if
if not bFnd then msgbox Textbox1.Text & " not found next to " & _
Combobox1.Value
End If
End With
 
Hi Tom,

Many thanks, could never found out this myself but I´am learning.

Brgds

CG Rosén
 
Back
Top