Subscript out of range error

[UTAS_Matt]

Hi all, I have just started experimenting with vba in excel. I have had some success and a bit of fun! However I'm going nuts trying to write one macro to check a users ID against a worksheet.

I have written a macro which first checks all drives for a specific user file, then the macro below pulls the contents of three cells and assigns them to variables "strUser", "sSTAMP" and "sACCESS" (all public strings).

Now the macro checks "sSTAMP" against cells from another worksheet found in another workbook within a row (row 17).

Please Login or Register  to view this content.

Whenever I run this I get "subscript out of range" at the IF command. Where am I making the mistake? Is there a better way of looking along a row for an entry?

BTW I'm using office 2010.

Many thanks

[Pepe Le Mokko]

Hi
please read forum rules and adapt your title accordingly - Thx

[UTAS_Matt]

Sorry, I am new. To be fair when quickly scanning through a very busy GUI it is difficult to spot.

[nilem]

maybe so

Please Login or Register  to view this content.

[Norie]

sFileLocation is never declared or given a value in the posted code.

[UTAS_Matt]

maybe so

Please Login or Register  to view this content.

Thanks nilem, I tried referencing using Cells (17,i) but got the same error.

The string sFileLocation is currently set to "P:\Personal\My Documents\MG Matrix.xlsm" from another module, when debugging the value is showing correctly. My "P:\" drive is a network drive which is sync locally for when I am off line. I am using Windows7.

[Norie]

You don't use the full path to refer to an open workbook via the Worbooks collection.

[UTAS_Matt]

Could you explain further? I dropped the Workbooks and worksheets reference and the code ran without error. However it did not find the entry from the worksheet "Matrix" that matched the variable held by sSTAMP.

[nilem]

Utas, try it

Please Login or Register  to view this content.

[Norie]

You definitely shouldn't drop those.

To refer to an open workbook called MG Matrix.xlsm you would use Workbooks("MG Matrix.xlsm").

If sFileLocation contians the path and filename you can get the filename like this.

Please Login or Register  to view this content.

So your code would look something like this.

Please Login or Register  to view this content.

PS Is the code in the matrix workbook? If it was then you could refer to it without using the name with ThisWorkbook.

[UTAS_Matt]

Thank you Norie!
That done the job. The code was infact within the active workbook but i wanted to keep everything explicit so that I could move the code at a later date.

[Norie]

I think nilem beat me to it.