Search Code

[Soniya]

Hi All,

I have the following code to search all the sheets in my
workbook for a give text in my userform1 textbox1

How can I get the code to search last charecters only
based on my textbox1.text.

i want get the code modified to get something like this.

if y=len(textbox1.text)

I want to search SStr in right(B:B ,y)

How I could modify my code to get this?


Sub SearchTkt()
Application.ScreenUpdating = False
sStr = textbox1.Text
For Each sh In ThisWorkbook.Worksheets
If sStr <> "" Then
Set rng = Nothing
Set rng = sh.Range("B:B").Find(What:=sStr, _
After:=sh.Range("B1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End If

TIA
Soniya

 

[Dave Peterson]

..find is pretty neat.

If it's the rightmost characters you're looking for, you can use:

Set rng = sh.Range("B:B").Find(What:="*" & sStr, _
After:=sh.Range("B1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Now you're looking for "*" & sStr in xlWhole.

This means you want it to end with those characters.

You can do the opposite if you're looking for leading characters, too. (sStr &
"*" and xlWhole).

 

[Guest]

Soniya;

To convert text to a number use the VAL function.
dim intI as integer

intI = Val( string)

So using your textbox text looks like this.
intI = VAl( textbox1.txt)

To search for one string within another use InStr( strToSearch,
strToSearchFor, intStart)

dim intRow as integer

for intRow = 1 to 99
if InStr( Right( activeWorkheet.Cell(intRow,intColumn).Value, intI), SStr,
1) then
'Do what you wanted to do.
endif
next intRow

You may use a Do While loop using isEmpty to stop when you reach an empty row.

You might want to check the text going into the textbox to make sure you
only enter
numbers. This is done by putting code into the textBox1_Keypress() and
textBox1_OnChange() events.

Hope this helps. I didn't code and test it.

 

[Guest]

Thanks Dave,

Yur code helped me..!

-----Original Message-----
..find is pretty neat.

If it's the rightmost characters you're looking for, you can use:

Set rng = sh.Range("B:B").Find(What:="*" & sStr, _
After:=sh.Range("B1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Now you're looking for "*" & sStr in xlWhole.

This means you want it to end with those characters.

You can do the opposite if you're looking for leading characters, too. (sStr &
"*" and xlWhole).


--

Dave Peterson
(e-mail address removed)
.