Excel - String Search

#1
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.
 
#2
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
 
#3
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.
 
#5
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
 
#11
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.
 
#12
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
 
Top