Find - VBA

[Paul Morris]

Hi All,

Having some problems with the below - the line starting with 'se
findit...' is returning the error Subscript out of range.

Have checked, double checked and triple checked the path names and the
are correct, and the variable 'SKU' contains the correct value.

Can open up the 'vintages - current' workbook and do the find, whic
works, but this causes problems further down the track.

Would appreciate any assistance you're able to offer.

Thanks,
Paul




Sub InsertItems()

Dim line As Integer


line = 18
SKU = Range("B" & line)

'Find SKU if it is in supercession list
Set findit = Workbooks("G:\Logistics\Product Order Form\Vintages
current.xls").Sheets("Sorted By Materia
Description").Range("a:a").Find(SKU

 

[Chip Pearson]

If would be helpful if you posted the code that it causing the
problem.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Paul Morris"
in message
news[email protected]...

 

[Paul Morris]

Hi Chip,

I did, it's:

Set findit = Workbooks("G:\Logistics\Product Order Form\Vintages
-current.xls").Sheets("Sorted By Material
Description").Range("a:a").Find(SKU)


Thanks,
Paul

 

[CoRrRan]

Hello Paul,

The problem is, is that you want to reference a workbook that is not in the
active memory. I.e. the Workbooks()-object requires either a workbook
number (ID of workbook in active memory) or the name of the workbook (in
your case "Vintages - current.xls").

You can compensate this by using the following code:
**************************************************************
Dim wkbMain as Workbook

Set wkbMain = Workbooks.Open("G:\Logistics\Product Order Form\Vintages -
current.xls")

Set findit = wkbMain.Sheets("Sorted By Material
Description").Columns(1).Find(SKU)

<...code...>

Set findit = Nothing
Set wkbMain = Nothing
**************************************************************

Now you won't see the workbook being open, but still be able to access it.

Don't forget to set the wkbMain-object to nothing after you finish.

HTH,
CoRrRan

 

[CoRrRan]

Quote: "Now you won't see the workbook being open, but still be able to
access it."

Sorry, this is not the case, the workbook does gets opened in the active
memory. My mistake of posting before fully testing my code...

HTH,
CoRrRan