VLookup

[THarte]

How does this function work?

Here is my application: I have several rows of data that I have entered via a userform. I need to be able to search for individual records, move them somewhere, pull them back into the userform, update them then overwrite the old cells with the new content.

I have tried a few ways and am having no luck. I read in this forum where maybe the VLOOKUP function would help me accomplish this.

Any help is appreciated.

 

[DaleWatson123321]

THarte,

Perhaps the VLOOKUP function might be a solution, and someone will contribute adequate assistance to resolve your task.

However, seeing as you want to... "be able to search for individual records, move them somewhere, pull them back into the userform, update them then overwrite the old cells with the new content"... it appears as though you should consider using Excel's "database functions".

These "database functions" are an extention of "Data Filter" - i.e. "Data - Filter - Advanced Filter". This functionality includes being able to selectively extract data to a separate sheet, based on "criteria" you specify. There are also database functions that allow for generating summary data.

Perhaps you might want to wait to see what others can offer via the VLOOKUP route. However, if you run into "roadblocks", I'd be interested in "lending a hand" with my knowledge of these "database functions".

If you do decide to involve me, I would ask that you email me a copy of your file, so as to enable me to "zero in" on the specifics of your situation - to save us both time. If you happen to have any "sensitive data", please replace it with "fictitious data" that will still be "representative" of the type of data you're working with.

Hope this "gets things rolling".

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

 

[xlbo]

Vlookup works by picking up associated row values (Hlookup for associated column values) - ie you need something to match. If you are just looking for a particular value / text, you're gonna need to use the Find function in VBA
More details please
Geoff

 

[THarte]

Geoff

I am looking for unique identifiers for a record like Policy Number then pull up the unique record. Based on your the responses looks like the Find or Database Function....?

 

[xlbo]

Ok - this is what I'm assuming the situation is. You want to enter a policy number and see, in other textboxes, some related details regarding that policy number (poss start date / expiry date / payments etc). You then want to be able to change these details and insert them back into the original position in the sheet.....
Am I right. If so, I can probably help and yes, vlookup would be one way to do it although it would probably be quicker (and easier, functionality wise) to use code and the offset function to pick up the values. Same goes for re-entering the new values.
Please let me know
Geoff

 

[THarte]

You are exactly right Geoff. THat is what i am trying to accomplish.

Any help would be appreciated.

 

[xlbo]

Ok - so let us assume you have a textbox called tPolNo into which you wish to enter a policy number.
Just for testing, we are going to have 2 further textboxes caled tExpdate and tPayment (obviously, this is just an example so swap names etc for your names)
Your form is called ufPol and your data is on a sheet called Data with the policy number in column A and other details about each policy, stretching horizontally across the sheet. You also have 2 buttons called bGetData and bChgData.
The process will be:
Enter policy number in tPolNo. Press bGetData. This will update the other textboxes with relevant info.
You change any data in the other textboxes and then press bChgData to re-enter into the sheet - overwriting the existing data.
If this seems like the kind of process you would go for then please reply in the affirmative and I will come up with some code
Geoff

 

[xlbo]

THarte - I've noticed that you have various other threads in this and the VBA forum which also seem to pertain to your query - the VBA one especially looks like you have got further with your coding than it appears here. IMHO, it is better to have one thread for the whole problem as you'll then (hopefully) get a seamless response rather than several that you have to join together - it is also easier for people to help if they know the whole objective rather than reacting to parts of it....
Geoff

 

[THarte]

Makes sense. I will makes sure to do that in the future. I am new to both forums and am not aware of the members for both so I posted my issue in both.

I am still interested in how the VLookup works if you know of anything I can reference to learn more in case what I used this time as a solution is not appropriate for future needs.

 

[xlbo]

No worries - I would say that most of the VBA forum are also members of the Office forum.

Vlookup, looks up a value in a range and gives a result of a set number of columns to the right
eg Col A = policy number, col F = start date
your formula would be =vlookup(policy number,yoursheet!A:F,6,false)

this would return the start date

In terms of what you want for your form, you could use - on button click event,
stDate = worksheetfunction.vlookup(tPolNo.text,yoursheet!yourRange,no. of columns across,false)
-this assigns the lookup value to a variable
-where yoursheet!yourrange is something like Sheet1!A:F
and no. of columns across is an integer

me.tStDate.value = stDate
-this enters the variable as the textbox value

HTH
Geoff

 

[SkipVought]

hi, THeart,

Folling this same general approch, let me add my spin...

I like to use Named Ranges. EVERY table that I manipulate, I apply Named ranges to using the Names in the top row of the table. The table MUST be contiguous, If it's not, then not even the vlookup or any other functions will work consistantly.

One the table ranges are named, I use a dual method of 1) locating the first record that matches a column value and then 2) retreiving the other values in the located row and then 3) determining wheether the next row needs to be processed, assuming that there are multiple rows for a particular lookup value and that the table is sorted by that row.

vLookupRow = Application.Match(LookupValue, LookupRange, 0)
Do while Not IsError(vLookupRow)
   'Have a row, now get the other values
   Value1 = Value1Range(vLookupRow, 1).Value
   Value2 = Value2Range(vLookupRow, 1).Value
   ...
   'now see if we have another row
   vLookupRow = vLookupRow + 1
   vLookupRow = Application.Match(LookupValue, LookupRange, 0)
Loop

The code that I use to name the ranges in a table is ...

   Application.DisplayAlerts = False
   [A1].CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
   Application.DisplayAlerts = True

I like this better than using Vlookup, because the entier table has to be referenced and the column references are lost in the function formula. Which is more discriptive?

MyValue = Application.Vlookup(LookupValue, LookupRange, 5, False)
OR
MyValue = Balance(vLookupRow, 1).Value

where column E, (col 50 is "Balance"

Hope this helps


Skip,
metzgsk@voughtaircraft.com