 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008Vlookup syntax format
Hi the floowing line is coming from the excel help VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) How can i use this function in macro where the lookup_value is in A1 format, the table_array is a variable (rng) ? the lookup funtion is rffered to another sheet in the same workbook e.g. sheet1 is where the function works but the reference array is in sheet2. Yossi, Maybe:- Sub stance() Set rng = Sheets("Sheet2").Range("A1:B10") lookupvalue = Sheets("Sheet1").Range("A1").Value myvalue = Excel.WorksheetFunction.VLookup(lookupvalue, rng, 2, False) MsgBox myvalue End Sub Mike > Hi > the floowing line is coming from the excel help [quoted text clipped - 3 lines] > the lookup funtion is rffered to another sheet in the same workbook e.g. > sheet1 is where the function works but the reference array is in sheet2. Hi i haven't tested it yet but rng is changing for eacj workbook so i need the macro to find it itself e.g. run "currentreggion" and get the range but i can't make the syntax, can you help? > Yossi, > [quoted text clipped - 16 lines] > > the lookup funtion is rffered to another sheet in the same workbook e.g. > > sheet1 is where the function works but the reference array is in sheet2. Hi Now i tested it, i modifyed the syntax a bit but i get run-time error 1004 "unable to get Vllookup property of the WorksheetFunction class here is the syntax Sub Add_TRF_CounterId() Rows("1:1").Select Selection.Insert Shift:=xlDown ' insert empyt row Range("A1").Select 'select cell a1 to run vlookup Set rng = Sheets("57, P_NBSC_SERVICE").Range("A2:c300") lookupvalue = Sheets("Sheet 1").Range("A2").Value 'the next line gives the error massage myvalue = Excel.WorksheetFunction.VLookup(lookupvalue, rng, 3, False) End Sub > Yossi, > [quoted text clipped - 16 lines] > > the lookup funtion is rffered to another sheet in the same workbook e.g. > > sheet1 is where the function works but the reference array is in sheet2. That means that there wasn't a match. Option Explicit Sub Add_TRF_CounterId() dim lookupvalue as variant dim res as variant with worksheets("sheet 1") .Rows(1).Insert lookupvalue = .range("a1").value end with Set rng = Sheets("57, P_NBSC_SERVICE").Range("A2:c300") res = application.VLookup(lookupvalue, rng, 3, False) if iserror(res) then res = "No Match" end if msgbox Res End Sub > Hi > Now i tested it, i modifyed the syntax a bit but i get run-time error 1004 [quoted text clipped - 35 lines] > > > the lookup funtion is rffered to another sheet in the same workbook e.g. > > > sheet1 is where the function works but the reference array is in sheet2.  SignatureDave Peterson Is there anything magic about doing this with dates in column A? I copied Yossi's "stance" toy example and using integers in columns A and B all works well. If I change the column A data to dates then the program always reports the runtime error. This is whether the dates are typed simply as text, or whether they're created by using a Date( ) function on the worksheet. On Sheet2 I have a column of consecutive dates. Then I do a copy/paste onto Sheet1 so I know they are identical. Yet when I run the routine it invariably fails with that same runtime error about "Unable to get the Vlookup property of the WorksheetFunction class" Can one not use VLookUp in VBA to search for dates, or am I overlooking something fundamental? It works fine from the worksheet, just not from VBA. Thanks. Bill ----------------- > That means that there wasn't a match. > [quoted text clipped - 63 lines] >> > > sheet1 is where the function works but the reference array is in >> > > sheet2. First, I used application.match (not application.worksheetfunction.match) and I avoided any run time error. But I did test the result with an "if iserror(...)" statement. Second, sometimes converting the date to long will help: dim res as variant res = application.vlookup(clng(yourdatevar), yourrange, 2, false) if iserror(res) then 'no match else msgbox "match on row: " & res end if > Is there anything magic about doing this with dates in column A? I copied > Yossi's "stance" toy example and using integers in columns A and B all works [quoted text clipped - 87 lines] > > > > Dave Peterson SignatureDave Peterson Oops. Make that application.vlookup() and application.worksheetfunction.vlookup()--but they both suffer the same malady. > First, I used application.match (not application.worksheetfunction.match) and I > avoided any run time error. But I did test the result with an "if iserror(...)" [quoted text clipped - 105 lines] > > Dave Peterson SignatureDave Peterson Using "Application.Vlookup" does stop the runtime error. It returns a value of "Error 2023" though which is not useful. Abandoning the Vlookup function and going to Application.Match as you said runs ok, and returns a value of "Error 2042". Typing the lookupvalue as LONG or DOUBLE or SINGLE does seem to work properly. Using type STRING or DATE returns the error code. So I guess the bottom line is that I can build something up around MATCH and it will work. Though it makes me very uneasy that other things which should also work do not. I'm tempted to do a manual binary search to find the key rather than relying on the VBA call. What to do, what to do....? Anyhow, thanks for your help Dave. You nailed it. (Incidentally, are we spinning our wheels using VBA anyhow now that Microsoft says they're going to kill it? Basically VBA is the only reason I've continued to be tied to Windows. If they kill that I may make the break to Linux with Open Office or some such. Have you worked with the tool Microsoft says we should be migrating to in place of VBA?) Bill ------------------------------------ > First, I used application.match (not application.worksheetfunction.match) > and I [quoted text clipped - 110 lines] >> > >> > Dave Peterson Checking the result of the application.vlookup() with iserror() should be useful. Did you try using application.vlookup(clng(yourdatevar), ... )? Didn't they announce the killing of VBA for Macs? And no, I haven't looked at any replacement language. ps. I didn't mean to suggest abandoning application.vlookup(). But if clng() didn't work, then that WAS going to be my followup suggestion! > Using "Application.Vlookup" does stop the runtime error. It returns a value > of "Error 2023" though which is not useful. [quoted text clipped - 137 lines] > > > > Dave Peterson SignatureDave Peterson What I read was that Microsoft wasn't putting VBA with the latest Mac Excel, but don't worry -- they're also removing it from PC Excel with the next release. As I recall, the effective date for removal of all support was something like 2010. However when I search the Microsoft web site now I don't find the stuff that was there before about when support ends. Now I find this bit posted in a Microsoft blog: http://blogs.msdn.com/excel/ (See the Jan 16 posting). It claims they will not drop it from the PC. I also found this link describing why it was too difficult to maintain VBA on the Mac. Lots of assembly level stuff going on beneath the covers: http://www.schwieb.com/blog/2006/08/08/saying-goodbye-to-visual-basic/ Anyhow, that's not an immediate concern. Thanks for the help Dave. Bill > Checking the result of the application.vlookup() with iserror() should be > useful. [quoted text clipped - 167 lines] >> > >> > Dave Peterson Thanks for the links. (I'm not too concerned about the rumors concerning wintel PCs and VBA, either.) > What I read was that Microsoft wasn't putting VBA with the latest Mac Excel, > but don't worry -- they're also removing it from PC Excel with the next [quoted text clipped - 189 lines] > > > > Dave Peterson SignatureDave Peterson But you didn't answer my question... Did application.vlookup(clng(yourdatevar), ... work ok? > What I read was that Microsoft wasn't putting VBA with the latest Mac Excel, > but don't worry -- they're also removing it from PC Excel with the next [quoted text clipped - 189 lines] > > > > Dave Peterson SignatureDave Peterson Sorry I skipped over responding to that question. I tried a variant of it and it failed with VLOOKUP but worked with MATCH. What I did was to DIM the key as LONG and then used that key for the search. Since I still have the scrap of code though I went back and did it exactly as you asked about. It still fails with VLOOKUP and still works with MATCH. Bill -------------------------------- > But you didn't answer my question... > [quoted text clipped - 213 lines] >> > >> > Dave Peterson This worked ok for me in xl2003: Option Explicit Sub testme() Dim myDate As Date Dim myRng As Range Dim res As Variant myDate = DateSerial(2008, 2, 3) Set myRng = Worksheets("Sheet1").Range("A:E") res = Application.VLookup(CLng(myDate), myRng, 2, False) If IsError(res) Then MsgBox "Not found" Else MsgBox res End If End Sub > Sorry I skipped over responding to that question. I tried a variant of it > and it failed with VLOOKUP but worked with MATCH. What I did was to DIM the [quoted text clipped - 227 lines] > > > > Dave Peterson SignatureDave Peterson Ok, your example works on my system (also Office 2003). So I set about changing things in my other routine one line at a time and suddenly it worked. Turns out when I started dinking around with MATCH rather than VLOOKUP, I changed the Rng from an array to a vector to make it work. However VLOOKUP fails with a vector since it expects to get its result from another column. Pilot error. Bill ------------------------------- > This worked ok for me in xl2003: > [quoted text clipped - 277 lines] >> > >> > Dave Peterson I feel better! And sorry about that initial typo with =match(). I sent you down the wrong path by mistake! > Ok, your example works on my system (also Office 2003). So I set about > changing things in my other routine one line at a time and suddenly it [quoted text clipped - 293 lines] > > > > Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.