Vlookup/address question

[Dan]

Based on data entered (ID_Location) I need to extract information from
another worksheet. I have used VLookup successfully to extract the first
occurrance of the information.

VMM_Sig_Name = Application.WorksheetFunction.VLookup(Range(ID_Location),
VMM_Workbook.Worksheets(ShortVMM_FileName).Range("B6:P2000"), 3, False)

I do know the number of times the "ID" shows up in the other worksheet.

Num_Of_IDs_In_VMM =
Application.WorksheetFunction.CountIf(VMM_Workbook.Worksheets(ShortVMM_FileName).Range("B6:B2000"), myID)

What is the best way to extract all the information? Use multiple VLookups?
Obtain the address, in the other worksheet, of the first occurrance then
conduct a copy and paste?

Example:
ID to lookup, fisrt run = 100h, second run = 025h,

Lookup Worksheet information:

Column B Column D Column P
000h A Desk
000h B Chair
000h C Computer
100h A Apple
100h B Orange
100h C Pear
100h D Peach
100h E Grape
025h A Tomato
025h B Beans
025h C Cucumber
025h D Pepper

Thanks

[Tom Ogilvy]

Vlookup doesn't return the location. Find will and Match can be used to get
it.

Once you have it, then using resize to copy and paste would be recommended.

You were given code that shows how to use Find and Match. Suggest you
revisit these.

--
Regards,
Tom Ogilvy


"Dan" <[email protected]> wrote in message
news:[email protected]...
> Based on data entered (ID_Location) I need to extract information from
> another worksheet. I have used VLookup successfully to extract the first
> occurrance of the information.
>
> VMM_Sig_Name = Application.WorksheetFunction.VLookup(Range(ID_Location),
> VMM_Workbook.Worksheets(ShortVMM_FileName).Range("B6:P2000"), 3, False)
>
> I do know the number of times the "ID" shows up in the other worksheet.
>
> Num_Of_IDs_In_VMM =
> Application.WorksheetFunction.CountIf(VMM_Workbook.Worksheets(ShortVMM_FileName).Range("B6:B2000"),
> myID)
>
> What is the best way to extract all the information? Use multiple
> VLookups?
> Obtain the address, in the other worksheet, of the first occurrance then
> conduct a copy and paste?
>
> Example:
> ID to lookup, fisrt run = 100h, second run = 025h,
>
> Lookup Worksheet information:
>
> Column B Column D Column P
> 000h A Desk
> 000h B Chair
> 000h C Computer
> 100h A Apple
> 100h B Orange
> 100h C Pear
> 100h D Peach
> 100h E Grape
> 025h A Tomato
> 025h B Beans
> 025h C Cucumber
> 025h D Pepper
>
> Thanks

[Dan]

I was having difficulties with the implementation of Find and Match (per my
comments in the last post). VLookup had corrected the ability to locate the
fisrt etry but now will not work for the rest of my application.

I will review the Find/Match comments and try these functions again.

Thanks for all the help. It has been greatly appreciated.

"Tom Ogilvy" wrote:

> Vlookup doesn't return the location. Find will and Match can be used to get
> it.
>
> Once you have it, then using resize to copy and paste would be recommended.
>
> You were given code that shows how to use Find and Match. Suggest you
> revisit these.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Dan" <[email protected]> wrote in message
> news:[email protected]...
> > Based on data entered (ID_Location) I need to extract information from
> > another worksheet. I have used VLookup successfully to extract the first
> > occurrance of the information.
> >
> > VMM_Sig_Name = Application.WorksheetFunction.VLookup(Range(ID_Location),
> > VMM_Workbook.Worksheets(ShortVMM_FileName).Range("B6:P2000"), 3, False)
> >
> > I do know the number of times the "ID" shows up in the other worksheet.
> >
> > Num_Of_IDs_In_VMM =
> > Application.WorksheetFunction.CountIf(VMM_Workbook.Worksheets(ShortVMM_FileName).Range("B6:B2000"),
> > myID)
> >
> > What is the best way to extract all the information? Use multiple
> > VLookups?
> > Obtain the address, in the other worksheet, of the first occurrance then
> > conduct a copy and paste?
> >
> > Example:
> > ID to lookup, fisrt run = 100h, second run = 025h,
> >
> > Lookup Worksheet information:
> >
> > Column B Column D Column P
> > 000h A Desk
> > 000h B Chair
> > 000h C Computer
> > 100h A Apple
> > 100h B Orange
> > 100h C Pear
> > 100h D Peach
> > 100h E Grape
> > 025h A Tomato
> > 025h B Beans
> > 025h C Cucumber
> > 025h D Pepper
> >
> > Thanks
>
>
>