Script out of Range Error

Hello,

I have written a block of code to check if a sting appears in an array and if so, to make modifications on the line where a match is found. The Reference range and the array are on two separate worksheets. When I run this on a dummy spreadsheet to test the code, it works fine, however when I add it to my master spreadsheet (which has about 20 tabs, lots of database references, pivot tables and code already developed) I am getting a "Runtime-error '9': Subscript out of range" error. The specific line if code is in BOLD below. I have already checked and double checked that the worksheet names are correct and I am using the "Option Explicit" function to ensure no variables are undefined. Any thoughts? Thanks in advance!

(ps - the actual excel file has too much sensitive information to post screenshots so I cannot provide)

Please Login or Register  to view this content.

Hi,

maybe try

Please Login or Register  to view this content.

Ciao,
Holger

Hi, elevate_yourself,

you can work with Range and two or three variables or use cells and have the same (only switching the place for rows and columns)

Please Login or Register  to view this content.

You may pass the column string or number as a parameter to the sub as well if you want to (if you use a number for the column make sure to use Cells instead of Range).

BTW: we don´t see what´s in your worklbook - why do you use find and not WorksheetFunction.CountIf?

Ciao,
Holger

I suspect, you do not have data in column B. The array b is empty.

Please Login or Register  to view this content.

Hi, AB33,

lr stands the last used row in Column B, Area starts at Row 8 while Loop starts at row 2 - there might a good point no data being available due to different sizes.

Ciao,
Holger

Hi, elevate_yourself,

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and you may use your array (which I think isn´t necessary at all).

Ciao,
Holger