Problem With Match Function Excel

Help Studio
, ,
1

I have a weird issue with the match function in excel. I have an excel process scope which checks if a given name of the variable is present in the file. If the name exists, it performs a set of activities in the If block, whereas if it’s not found, then it opens notepad and includes the first name of the person in the Else block. I had no issue whatsoever previously, but now for unknown reason, it’s throwing an error in the If block where I have the Read Cell Value activity that says: “Read Cell Value: The range B-1 does not exist.” The name indeed does not exist since the address position is not found, so it should had jumped to the Else block but it didn’t. What might be causing this?

Edit: The match function is surrounded with a Try Catch. And if the name is not found, then in the system exception, I have an Assign activity set to NameExists = False. Above the try catch, the same Assign activity is set but to True.

3

@private_matter

You might debugging the workflow instead of Run
Debug File always stops when you caught with error

Hope this may help you

Thanks,
Srini

4

Debugging / Running has the same effect.

2023-05-03_170002

2023-05-03_170022

If an error occurs in the match function, it does not throw the system exception and update the variable NameExists to False. And if I remove the assign NameExists = True above the Try Catch, then all loops will result in being false even if they are indeed True which is still wrong.

5

@private_matter

Try this way

Move the Match function activity out from Try Catch
Now debug your process and wait Match Function to throw the error
There you can see the Exceptiontype in warning box

Now create the catch with the same exception instead of generic system.Exception

image

Hope this may help you

Thanks,
Srini

6

The match function does not throw any errors. It’s the read cell activity that does which is inserted in the If block. I disabled the try catch and still the error is the same. Read Cell Value: The range B-1 does not exist.

7

@private_matter

Can you check the Exception Type what you are getting in the box
Maybe you can run instead of Debug and try

Thanks,
Srini

8

show us a screenshot of your entire code, specifically the if condition and read cell.

by your error, it’s certain that your read cell range of “B-1” is bad syntax. I’m assuming “-1” is being returned from the match activity

9

2023-05-03_190533
2023-05-03_190533390×1396 42.3 KB

In the catch block, the system exception has assign activity NameExists = False

10

There is no exception type that shows in the output.

11

@private_matter

It’s still in the Try Catch and you wont get the exception box

That need to be outside of Try catch to see what type exception you are getting

Thanks,
Srini

12

as i said, the match activity doesn’t throw exception if the value is not found. the output address will return “-1” if not found. Please see the docs.

Save to - Save the value returned by the activity (the position of the matched value within the range). If the value to match is not found, -1 is returned.

Therefore, it doesn’t go to Catch section, and your boolean remains True… and then ReadCell exceptions out due to a bad address

Change your IF condition to:

ExactAddressMatch <> -1
13

Yes you’re 100% right. That made it work. I did not know that the match activity does not throw any errors. Guess its because Continue on error is not an option. I removed the try catch and deleted the variable NameExists and added the

to the If condition. Works well now. Thanks. Marked as solved.

14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.