Extracting info from a database

[J David Southwick]

Private Sub extract()
Dim I As Double
I = 1013
descript = WorksheetFunction.VLookup(I, Range("itemlist!Database"), 2)
Debug.Print descript
descript = WorksheetFunction.VLookup(I, Range("itemlist!Database"), 3)
Debug.Print descript
descript = WorksheetFunction.VLookup(I, Range("itemlist!Database"), 4)
Debug.Print descript
'......etc
End Sub

What I want to do is extract all the information pertaining to "I" ,which
fills 8 columns, with code that will bring it into code as an array.
I know how to send an array to a worksheet but can not find any examples on
how to populate the array from a worksheet.(In this case, my database)

Thanks very much

Dave

 

[Roger Whitehead]

Hi David, try this (Untested):

Private Sub extract() Dim descript(7) as String
Dim I As Double
I = 1013

descript(0) = WorksheetFunction.VLookup(I, Range("itemlist!Database"), 2)

Debug.Print descript

descript(1) = WorksheetFunction.VLookup(I, Range("itemlist!Database"), 3)

Debug.Print descript

descript(2) = WorksheetFunction.VLookup(I, Range("itemlist!Database"), 4)

Debug.Print descript
'......etc 'to descript(7)
End Sub

Alternatively type "Option Base 1" in the decalrations section of the
module; the Array elements will then be (1) to (8)

 

[raj]

J:

Check out this document and see if it helps:

http://www.microsoft.com/exceldev/articles/movs104.htm

NOTE: Once on the above page, search for "Writing data
from the Worksheet"

I found it helpful; hope it helps you too.

 

[J David Southwick]

Hi David, try this (Untested):

descript(0) = WorksheetFunction.VLookup(I, Range("itemlist!Database"), 2)
descript(1) = WorksheetFunction.VLookup(I, Range("itemlist!Database"), 3)
descript(2) = WorksheetFunction.VLookup(I, Range("itemlist!Database"), 4)


Alternatively type "Option Base 1" in the decalrations section of the
module; the Array elements will then be (1) to (8)

Thanks for your ideas Roger and Raj
Dave