Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 10th, 2010, 08:58 AM
Registered User
 
Join Date: Nov 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problems with specific names when using vlookup function in VBA

I have a combobox which enables me to choose a name from an array.
Depending on the name i choose, the macro shall repaint the userform labels with the corresponding numbers from the array. All this works fine. The problem is that the macro gives me an error 1004 " Unable to get the Vlookup property of the WorksheetFunction class" if a use a specific name in specific cell in the array. E.g. if put "Anders" or "Barry" in the third cell from the top in the left column i get the error message. If i use "Nathan" in the same cell it works fine.

I fo have a workaround for the problem, but im very curious to find out why excel target "special" names

array:

Risks Actions
Bob 5 11
Charlie 2 3
Anders 0 6
Terrance 1 0
Tor 3 7

vba code:

Private Sub combobox1_change()

Dim name As String
Dim myrange As Range

name = ComboBox1.Value
Set myrange = Worksheets("Sheet1").Range("D4:F9")
UserForm1.Label22 = WorksheetFunction.VLookup(name, myrange, 2)
UserForm1.Label23 = WorksheetFunction.VLookup(name, myrange, 3)
UserForm1.Repaint

End Sub
 
Old November 13th, 2010, 01:35 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default The names need to be in order

The names in your array need to be in alphabetical order for the vlookup function to work properly, though I believe if you explicitly set the last parameter, Range_Lookup, to false, that works as well. Alternatively, you could use the DGET function.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Schedule a VBA macro to run at a specific time marshall04b Excel VBA 8 October 31st, 2015 08:41 AM
Vlookup with VBA andygill Excel VBA 2 November 6th, 2008 06:44 PM
Schedule a VBA macro to run at a specific time peterlihh Other Programming Languages 0 October 24th, 2008 07:15 PM
Problem with VLookup and DATEVALUE in VBA Sharadk74 Excel VBA 2 April 25th, 2008 09:30 AM
VLookup, arrays, and worksheet names chp Excel VBA 0 April 7th, 2006 03:15 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.