Excel - String Search

janejeba

Member
Suggest some effective ways to search for a text in excel. (Output should be the cell reference)

- Input excel file contains 1000 of rows and columns
- Looping through each cell takes lot of time
- Need to reduce timing.
 

Sachin_Kharmale

Active Member
Hi Janejeba,
create new action in MS Excel VBO and named as Find
Design flow like bellow

View attachment 1565942730937.png


StartUp parameter will be
View attachment 1565942794683.png


Code Stage input parameter will be
View attachment 1565942865846.png


code stage output parameter
View attachment 1565942897350.png


Code stage code will be

View attachment 1565942939789.png

Then just passed values from process and you will get output as cell reference of first occurrence of string
View attachment 1565943036258.png
 

janejeba

Member
Hi Sachin, Thanks for the support.

I'm not familiar with code stage. What are the name spaces and language settings I have to add for writting this code.
 
Hi Sachin
Thanks in advance...!

I am getting below error
Page: find cell refrence
Stage: Code to find
Type: Error
Action: Validate
Description: Compiler error at line 3: 'CellReff' is already declared as a parameter of this method.
Repairable: No
 

Shameer

New Member
Hi Janejeba,
create new action in MS Excel VBO and named as Find
Design flow like bellow

View attachment 4450


StartUp parameter will be
View attachment 4451


Code Stage input parameter will be
View attachment 4452


code stage output parameter
View attachment 4453


Code stage code will be

View attachment 4454

Then just passed values from process and you will get output as cell reference of first occurrence of string
View attachment 4455

Will this Only works for first occurrence?
I have a scenario where i need to get all the Cell references in which Cell Values are YES.
 

NathanRuiz

New Member
Hi shameer,
Unfortunately this will result you only the first occurrence, what you can do is put this code inside a loop and use the cell reference you get as the AFTER input in the find function, this will give you the next occurrence, you can keep looping until there is an exception thrown when the function finds nothing and store every output cell in a collection
 

Dorian K

New Member
Hello guys,

First of all thank you for this VBO.

However I have an issue, I did all like you mentionned above but I still have an error :

1601906568209.png

Any idea on how to fix it ?

Thank you in advance,

Kind regards,
Dorian K.
 

NathanRuiz

New Member
Hello guys,

First of all thank you for this VBO.

However I have an issue, I did all like you mentionned above but I still have an error :

View attachment 5723

Any idea on how to fix it ?

Thank you in advance,

Kind regards,
Dorian K.

This happens when the value is not found
A simple fix would be setting the find in a object and checking if it is nothing before getting the reference
Ie.
Dim cell as object
Cell = find(....)
If not cell is nothing
Cell.activate
Cellref=GetInstance(handle)Active cell.Address(false,false)
Else
Cellref=""
End if


That should fix it.
Good luck
 

RPACPG

New Member
Hi shameer,
Unfortunately this will result you only the first occurrence, what you can do is put this code inside a loop and use the cell reference you get as the AFTER input in the find function, this will give you the next occurrence, you can keep looping until there is an exception thrown when the function finds nothing and store every output cell in a collection

Hey guys,

Pretty new at this... Would someone be able to show how to alter the code to run it for more than one occurrence?
 
Top