Trying to Find selected cell content within another worksheet cell range.

[Craigside]

Hi

I have a large SS containing details of 600+ artworks, adjacent to which I have multiple worksheets each containing details of where particular paintings are on display.

Each item has a unique 'Ref No'. What I want is to select the cell containing the 'Ref No' then activate a macro via keystroke.

I need the macro to copy the 'Ref No', select the range of cells on my main worksheet and then 'Find' that unique 'Ref No' and go to it.

I have a simple macro to select the cell range on the main worksheet and open the Find dialog however I can't get the copy/paste into the Find dialog to work? What I have to do is manually type the 'Ref No' into the Find dialog and press Enter, I would like to automate this process.

Please Login or Register  to view this content.

I have attached a representative example of what I am trying to achieve.

Any help much appreciated!

[rvasquez]

Hello there try this

Please Login or Register  to view this content.

to insert this code into your workbook

1. Press Alt+F8 on your keyboard.
2. Clear the macro name box and enter FindRefNo
3. Then select the Create option
4. In between the Sub FindRefNo( ) and End Sub copy and paste the above code.
5. Exit out of Visual Basic.
6. Press Alt+F8 again
7. Select the FindRefNo macro and then selection Options
8. Assign a Ctrl+ key to your macro. For this example place q in the empty box next to Ctrl+
9. Select Okay and close the macro window.
10. Select the cell whose ref number you want to search for and then hit Ctrl+q on your keyboard.

Let me know if this works for you!

Thanks!

[Craigside]

Hi

Yes it works... Only thing is it takes a while to run. Is this because my worksheet 'Detail List' is currently up to cell AFX2272 (and still growing). The area of cells I need to do the search within A1:AB2272. Is it possible to specify the 'range to'?

Many thanks

[rvasquez]

update the code as follows:

Please Login or Register  to view this content.

[Craigside]

WOW that's awesome! does it in milliseconds now.

Thank you very much!

[rvasquez]

No problem! Don't forget to mark this thread solved and maybe give me a little star tap if I helped!

Thanks!

[Craigside]

How do i close it as solved? I've looked but can't see how?

[Craigside]

Ok many thanks!