Searching Column for text in Excel

[mkjp2011]

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?

 

[SkipVought]

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

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mkjp2011]

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?

 

[SkipVought]

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]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mkjp2011]

I tried this, however Range must not be supported. Because it tells me 'Range' is not a record type. What could be the reason?

 

[SkipVought]

as Excel.Range, programmer!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mkjp2011]

I tried that already. Still the same error. Yes I am a programmer and I usually don't have to use forums.

 

[SkipVought]

The try declaring rng as Object

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mkjp2011]

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?

 

[SkipVought]

Please post your control code for this process.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]