PDA

View Full Version : Trouble with vlookup



niyrho
01-19-2009, 04:57 PM
I'm trying to use the private sub "change" with vlookup. I want it to, whenever I type in a certain cell, to find the closest match from a list on another sheet.

Havent gotten far at all on this. The book I got gives examples on the to use those functions for something different and I think its leading me in the wrong direction.

stanleydgrom
01-19-2009, 05:03 PM
niyrho,

Please explain in more detail.

And, please post your workbook - scroll down, and see "Manage Attachments".


Have a great day,
Stan

mdmackillop
01-19-2009, 05:03 PM
Can you post sample data so we can see what you are comparing? Please include your code attempts.

niyrho
01-20-2009, 02:38 AM
The problem is that I'm pretty new to this stuff. I didn't go to school for it or anything. I'm self edgucated. I just can't learn from a book. If I can see something I can pick it apart, then I know how it works.

I guess I could post the workbook, but there'd be no point. It's blank right now. How I build this thing will depend on what I get figured out. I'll give ya an example of the code I tried, but I'm sure I'm way off.



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "A1" then
WorksheetFunction.VLookup("A1", Range("B1:C10"),2,false)
End Sub


Thats all I had. Just trying to get it to work to figure out how it works. After I do that, then I'll be able to apply it elsewhere. Otherwise, I'll just find another way around it.

Aussiebear
01-20-2009, 03:16 PM
I think you should be using a Worksheet_SelectionChange event rather than a Worksheet_Change, based on your first post.

Simon Lloyd
01-20-2009, 03:26 PM
The problem is that I'm pretty new to this stuff. I didn't go to school for it or anything. I'm self edgucated. I just can't learn from a book. If I can see something I can pick it apart, then I know how it works.

I guess I could post the workbook, but there'd be no point. It's blank right now. How I build this thing will depend on what I get figured out. I'll give ya an example of the code I tried, but I'm sure I'm way off.



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "A1" then
WorksheetFunction.VLookup("A1", Range("B1:C10"),2,false)
End Sub


Thats all I had. Just trying to get it to work to figure out how it works. After I do that, then I'll be able to apply it elsewhere. Otherwise, I'll just find another way around it.Best of using the intersect method like this
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Appplication.WorksheetFunction.VLookup("A1", Sheets("Sheet2").Range("B1:C10"),2,false)
End if
End Sub

mdmackillop
01-20-2009, 04:04 PM
Hi Nyirho,
The VLookup is very simple. It's the "closest match" that I'm after. Are you dealing with numbers, text, alphanumeric? Is it next highest/lowest or simply closest. What if there are two answers? e.g. closest to 5 could be 4 and 6.

niyrho
01-21-2009, 04:15 AM
I'm not using selection change becuase I need it to use vlookup as you're typing.
I'm pretty much trying to get excel to autocomplete. So it will fill in data for me if its on a list and, if it's not, I can continue typing and when I'm done it will add it to the list.

And please, if anyone is thinking of replying with a link to ozgrid, don't. I've tried that method. It's useless to me.

niyrho
01-21-2009, 04:35 AM
Ok, I kinda know how the code has to work. I'm just not quite getting the language. This is what I want:

1) I want the macro to activate when a certain cell is changed, i.e. you type another letter.

2)I want it to find the closest match to what you've typed so far, in a list on another sheet.

3)I want it to use a lookup function to fill in the rest of the fields. Something like: vlookup(a1,sheet2 a1:e1,2,false) or whatever.

4)I want it to allow you to continue to fill everything out manualy if what you're looking for isn't on the list.

5)If what you filled out is not on the list I want it to be added to the list when I hit the save or print botton or something. (I know this will be in a different macro, but its still a function I need)


I can't seem to get this macro to be triggered by what I want it triggered by. And I don't know of an easier way to do it.
I dont' even know what function to use to get it to find a closest match.
And for the vlookup, I'm not sure why its not working. The example I have uses Dim functions, is that really needed for what I'm trying to do?

mdmackillop
01-21-2009, 05:23 AM
see Post #7.


I guess I could post the workbook, but there'd be no point. It's blank right now.

My code never works on blank workbooks either. :dunno

niyrho
01-21-2009, 05:37 PM
yeah, thanks for that last post. That was real helpful. I'll go find another forum.

mdmackillop
01-21-2009, 06:06 PM
I've asked twice for information. You have provided none, so how can you expect assistance?

mdmackillop
01-21-2009, 06:22 PM
Having looked at your other threads, I see you are in the habit of not taking them to completion (2 marked solved from 15 posts), nor, with a couple of exceptions, thanking those who tried to assist. Good luck with your new forum.

Simon Lloyd
01-22-2009, 03:56 AM
Of course someone who will never learn, nor wishes to....the kind of OP that wants the perfect solution delivered right to them!

I think memberships at other forums will be short lived too!

Bob Phillips
01-22-2009, 04:11 AM
When you do a Vlookup, presumably you want to do something with the result.

Your code just runs it.

This example displays the result



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "A1" Then
MsgBox WorksheetFunction.VLookup(Target.Value, Range("B1:C10"),2,False)
End Sub

Bob Phillips
01-22-2009, 04:12 AM
The other thing I forgot to mention is that when you use a worksheet function in VBA, you don't use a cell string refrence as you do in Excel, you use the range reference.