Replace Vlookup 1

[Dan15]

Here is the situation: I need to replace the standard vlookup funtion in a cell, with code that is executed with a button. Basically the code will do the same thing - the variable will be defined in the first part of the code - the second part of the code will look through a list of data on worksheet2, with the variable in the first part serving as the key. When it finds that variable, the data in the adjacent cells on worksheet2is returned to cells on worksheet1.

Thanks-

-Dan

 

[xlbo]

something like this:

sub vlookreplace()
fWhat = selection.text
tAdd = selection.address
set fCell = sheets("Sheet2&quot.columns("A&quot.find(fWhat, lookin:=xlvalues,lookat:=xlwhole)
if not fCell is nothing then
for i = 1 to 10
sheets("Sheet1&quot.range(tAdd).offset(0,i).value = fCell.offset(0,i).value
next i
else
msgbox "No Match Found"
end if
end sub
This uses the selected cell to pick up the match text. It will then pick up the 10 columns to the right of the match in sheet2 and transfer them to the 10 columns to the right of the selection (in sheet1) Rgds
Geoff

Estne volumen in toga, an solum tibi libet me videre?

 

[DaleWatson123321]

Dan,

Excel's Data - Filter - Advanced Filter is a definite option - but it's not easy to get started because Microsoft's help in this area is sparse.

I can provide an example, but ideally the example should be based on the specifics you require. If you'd like to send a brief mock-up of what you require, I'll write the VBA code to achieve your objective.

The example will likely serve as a real "eye-opener" as to the power and effectiveness of this "database" component of Excel.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[williards]

I'm having the same issue...but I don't understand your answer.

I have Worksheet 1, with an Equipment No. column. This column can have duplicate Equipment Nos. (the same number can appear in different rows in that same column). There is also a blank column that needs a location filled in.

I also have Worksheet 2, with an Equipment No. column (this one has no duplicates) and a Location column. For each cell in the Equipment No. column, there is a Location in the adjacent cell that is specific to that Equipment No. (like a database record).

For each cell in the Equipment No. column in Worksheet 1, I want to find the Equipment No. in Worksheet 2 that matches, then use the adjacent cell contents (Location, from Worksheet 2) to fill the Location cell for Worksheet 1 (the cell that is adjacent to the Equipment No. that was originally matched in Worksheet 1).

 

[xlbo]

Williards - the original question called for a coded version of VLOOKUP - which I have provided (I hope). This should give you a clue as to how to sort your problem - use the VLOOKUP formula !!

eg:
in sheet1, B2 (assuming headers in row 1 and equipment no. in colA) enter:
=vlookup(A2,sheet2!$A$2:$B$1000,2,false)

also assuming your data is in A2:B1000 in sheet2

Copy this formula down to the bottom of your equipment nos. in sheet1 - et voila - your location will appear (as if by magic)

Also - please do not piggy back on threads and try and post the question in the most suitable forum - the answer to this is a straightforward formula so you should really post in the MSOffice forum - NOT the VBA forum Rgds
Geoff

Estne volumen in toga, an solum tibi libet me videre?

 

[Dan15]

xlbo-

I finally got back to working on this project - thank you for the code, it works thus far, I will be attempting to modify it today.
Dale, the only problem I see with the filters is that the range is not a set parameter, it is always changing depending on how many line items are added to the proposal.

Thank you for your help-

-Dan

 

[DaleWatson123321]

Dan,

Regarding the number of items, it's a "standard" practice to include a routine that always updates the range name used to define the (current) size of the database. It's simply a matter of including this subroutine PRIOR to running the other routine(s). I normally use the range name "data" to define the data range, and it's really easy (and transparant) to adjust the coordinates of the range name.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca