Worksheet_change error

[walduxas]

Hi,

I'm having a little trouble finding what's wrong with my code. The code is used in this situation: in sheet "Pavedimai" user has to enter security's code. Then the project tries to find this code in sheet "Isin". If project finds security code in sheet "Isin", then it copies security's name from sheet "Isin" to sheet "Pavedimai". If project can't find security's name in sheet "Isin", then it posts message box.

Everything goes perfect in sheet "Pavedimai", but I have a lot of trouble trying to search VP code in sheet "Isin". The first problem was trying to force project to select range "A1" in sheet "Isin" because code

Please Login or Register  to view this content.

didn't work due to some reason. I overcame this issue by using code

Please Login or Register  to view this content.

instead.

The second problem is in line marked with the stars

Please Login or Register  to view this content.

.
Due to some reason code

Please Login or Register  to view this content.

remembers cell value in sheet "Pavedimai" although at this moment sheet "Isin" is selected. And I can't understand why this code is unable to select particular range in sheet "Isin" and remember that range value.

Does anyone of you have any ideas what's wrong with this code?

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Try this

Insted of This code

Please Login or Register  to view this content.

Use this code

Please Login or Register  to view this content.

[walduxas]

I've tried that already in this case I get error message "Run-time error '450': wrong number of arguments or invalid property assignment".

[kvsrinivasamurthy]

Due to Err.Number <>0 the following code may be selecting "Pavedimai" Sheet.

Please Login or Register  to view this content.

[walduxas]

That's not the case. I tested my code using watch window and running it step by step. That error handling code does not open sheet "Pavedimai".

However, I think I found a solution to all my problems. I replaced code

Please Login or Register  to view this content.

with code

Please Login or Register  to view this content.

In modules section I inserted new module and created new function:

Please Login or Register  to view this content.

In this case no errors occur and the code runs very smoothly
Anyway, thank's for your help