search method of range class failed

[sarahng86]

Hi,

I'm new to vb - and have been working on this project for a couply of weeks -
i'm trying to fill my listbox2 on worksheet2 based on selection in a listbox1 on worksheet1

this code worked for me at first
but it stops working after awhile

I've tried all the methods i've seen online - including making this a macro and calling the macro when the listbox is selected

i've also tried all sorts of ways , including the method

Please Login or Register  to view this content.

instead of activate

my code's below - the area in red is the problem one

PLEASE HELP !! thanks !

Please Login or Register  to view this content.

[royUK]

Selecting or Activating the worksheet is not necessary. It would be more efficient to use the ListFillRange of the Listbox than looping through cells like your code does. Can you attach te workbook?

[Excelenator]

What is the purpose of this? Do you intend to declare a NUMBER as a variable? What purpose does this serve? This is not allowed by the compiler.

Please Login or Register  to view this content.

Change the number to a letter and then you may have some luck. Also, you don't need to SELECT cells to get their values. This is inefficient.

[Leith Ross]

Hello sarahng86,

The referencing problem is from using syntax like Sheet16.Range("B2").Seelct. Worksheet formulas allow you to designate a worksheet name this way, but it is a little more involved in VBA. The worksheet's name is actually a key value into the Worksheets Object Collection. Here is how your reference should be...

Please Login or Register  to view this content.

The Sheets Object Collection also includes Chart Sheets, while Worksheets does not. There are other problems, but we will take them one at a time.

Sincerely,
Leith Ross

[shg]

Sarah,

Assuming Sheet4, Sheet6, and Sheet16 are code names rather than sheet names of your worksheets, that aspect of your code is fine.

It took me a few minutes to prototype a workbook, but this worked for me:

Please Login or Register  to view this content.

See attached.

[sarahng86]

thanks to all of you for helping =)
yeps ive tried ur suggestions n it works =)

[sarahng86]

Hi,

I applied shg's code

just wondering if i can ammend it in this way to also populate another listbox as below - the 2nd listbox isn't populating - so not sure what's the problem

Please Login or Register  to view this content.