XLFind not working with many worksheets

[drunkenmonkey]

Hi!
I have this XL file with many worksheets.
I want to find the a cell's value. Simple as that.
The xl function result is false and the returned value
from Val_PDBase var is Year.
Strange because the value to look for XLFind is located in All columns sheet, row 1 col 21.
Wonder what is the problem.

Here's the link to the file
https://u.pcloud.link/publink/show?code ... lfHHA7TdXV

Here's the sniplet code

Code: Select all

XLOpen>%USERDOCUMENTS_DIR%\mybook.xls,0,xlBook
Let>val_1=Basic Coverage Guaranteed Life Insurance Amount
XLFind>%xlBook%,All Columns,%val_1%,isFound
XLGetSelectedCell>%xlBook%,Val_PDBase,Row_PDBase,Col_PDBase
XLQuit>xlBook

Thank you for your help!

[Dorian (MJT support)]

I've found that it helps if you select the correct sheet before using XLFind. You can do this with XLRunCode. Your script works as expected once we do that.

Code: Select all

XLOpen>%USERDOCUMENTS_DIR%\mybook.xls,0,xlBook
Let>val_1=Basic Coverage Guaranteed Life Insurance Amount

//Select the correct sheet
XLRunCode>xlBook,Sheets("All Columns").Select

XLFind>%xlBook%,All Columns,%val_1%,isFound
XLGetSelectedCell>%xlBook%,Val_PDBase,Row_PDBase,Col_PDBase
XLQuit>xlBook

Important :

For XLRunCode to work 'Trust Access to the VBA project object model' must be enabled in Excel:

In Excel go to File/Options -> Trust Center -> Trust Center Settings -> Macro Settings.
Under 'Developer Macro Settings' enable 'Trust Access to the VBA project object model'

[drunkenmonkey]

Thank you Dorian, works good but the returned value for Col is 22
and it should be 21, can you tell me why.
Thank you so much for your help.

[Dorian (MJT support)]

That's very odd, as for me it's 21 (Col U) as expected. I'm scratching my head as to why this may be.

COL_PDBASE=21
ROW_PDBASE=1
VAL_PDBASE=Basic Coverage Guaranteed Life Insurance Amount

Although I have very occasionally seen Excel scripts not work as expected when Excel requires an update. So maybe look into that?

I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2207 Build 16.0.15427.20166) 32-bit.

[drunkenmonkey]

Hi!

On my side Excel same version
https://u.pcloud.link/publink/show?code ... iN9SNiTGzV
thank you!

[Dorian (MJT support)]

This may be a silly question but was the file I downloaded exactly the same as the one you're using? And are you using Macro Scheduler 15.0. 20?

[drunkenmonkey]

Hi!
Yes for both question. running on Win 10
https://u.pcloud.link/publink/show?code ... 0t3XuqtgpV
Thank you!

[Dorian (MJT support)]

I'm completely unable to replicate this. Nothing I do forces it to misbehave.

[drunkenmonkey]

Hi Dorian,
I must have used another file because I downloaded the posted XL file and col no is 21.
My apologies for misguided you.
Have a great day and thank you for your precious help.

[Dorian (MJT support)]

Aaaah, that's good to know!