Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Code
..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). Soniya wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Code
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. "Soniya" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Code
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). Soniya wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code for search button | Excel Worksheet Functions | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Search code | Excel Programming | |||
File Search code | Excel Programming |