Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Searching Column for text in Excel

Status
Not open for further replies.

mkjp2011

Programmer
Aug 24, 2010
15
US
I am trying to search the first column of my excel document using EXTRA! Basic. How would I search that column for a specific text value and then return the row it was found out? Is this possible?

 



Code:
dim xl as excel.application, lRow as long
...
lRow = xl.YourSheetObject.Columns(yourColumn).Find("YourFindValue").Row


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
This works great! However now I run into the problem, where if that certain text is not found then it returns an unknown value and stops my script. How do I handle the situation if the value is not found?
 


Code:
dim xl as excel.application, lRow as long, rng as range
...
set rng = xl.YourSheetObject.Columns(yourColumn).Find("YourFindValue")

if not rng is nothing then 
  lrow = rng.row
end if

...

set rng = nothing

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
I tried this, however Range must not be supported. Because it tells me 'Range' is not a record type. What could be the reason?
 


as Excel.Range, programmer!

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
I tried that already. Still the same error. Yes I am a programmer and I usually don't have to use forums.
 


The try declaring rng as Object

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
I tried that yesterday and it worked. However once I do this, if it comes across something that is not in that column then the macro stops the macro and says Object value is set to Nothing; stopping macro playback. How could I avoid this?
 



Please post your control code for this process.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top