filling up values in a combo box

[grkchakri]

Hi Alll

I have a combo box on sheet 2 . I want to fill this with the values from sheet1 range("a1:a50"). how to do this.
or is it better to use
A list box to a range and populate values.

In either of the above two scenarios. can we type a part of the text and get those values. Example there 50 countries when i type in "H" can it show me in the dropdown where countries name start with "H". Is this possible in combox box / list values / list box

Is it always better to use combox box / list box on userform rather them placing on a sheet and playing with the code.

Many thanx for your help

[gmr4evr1]

Yes, you can use a ComboBox. In the properties window of the ComboBox, set the RowSource to the sheet# and range ('Sheet1'!A1:A50). As far as the typing "H" and all "H" countries show, I don't know how to do that. But, I do know that when you type "H" it will bring up the 1st country that starts with "H" and the next one each time you type (hit) "H" after that.

[grkchakri]

In continuation to the above when i continue to select a value in the combo box to check the value in column (ex:3/4 so on) using vlookp. why does it throw the errors "n/a" below is the code and the workbook.

ab1.png

Please Login or Register  to view this content.

Book1.xlsm

[jaslake]

Hi grkchakri

You've attached a Picture...can't work with that...please attach the File (WITH EXISTING CODE).

[grkchakri]

i did attach the book now.

[gmr4evr1]

Question....You have Dim z as string, but z is not used in the code....how come?

[grkchakri]

Generally i have a habit of keeping some variables ready, though i might /might not use them. Z has got nothing to do in the code, u can use it if you want to alter the code

[jaslake]

Hi gmr4evr1

I see you're back on line...I'll leave this to you as you're been on it and are following it.

Please note, the OP is using VLookUp which requires Data to be sorted in ascending order.

[gmr4evr1]

Howdy John,

I am kind of lost on this one. When I ran the code, it would return the month for certain countries, others would return the correct #, and still others would return a number that had nothing to do with that country. I never did get it to return a value of N/A though.

I did try using z in the code in various places, but the only change it ever made was returning a blank in e14.

[grkchakri]

Yes if i understood it is right
1) when text values are searching through Vlookup it should be in ascending order. Not nesccary when you are using numbers as you were look up value
2) when text is involved is it better to use index and match functions

[jaslake]

@gmr4evr1

I didn't either

I never did get it to return a value of N/A though.

[sktneer]

Few things....

1) You have left the last argument in Vlookup function, it should be 0 or false for an exact match.

2) Why not to use a simple drop down list through data validation in a cell and referencing that cell in the formula, what is the need of a combobox placed on the sheet?

3) If you are trying to get a value from the range C4:N11 based on country and month, index/match would be the right choice.

4) Even if you use index/match, where is the month criteria placed on the sheet? I don't see any drop down for months.

Probably you need to explain your requirement correctly.

[jaslake]

@grkchakri

Your Lookup is text...x is text

Please Login or Register  to view this content.

[gmr4evr1]

@grkchakri
Adding a 0 to this line does make a big difference (credit to sktneer)

Please Login or Register  to view this content.

Although I'm not sure if its returning the correct value for New York, but the others seem to be correct

[grkchakri]

i Think the problem was first the values should be in ascending order when the value is in text format (Though i am not sure y) and added in the fourth parameter 0 for an exact match and i was able to get the values for the month jan and so on

sktneer: I was trying to understand and check how to use a combox with vlookup and vlookup value being text.
As u said a data validation simple one or index/match is the best i agree.

Thank you guys (gmr4evr1,jaslake,sktneer) for your suggestions. Below is the code where it worked to give required info.

Please Login or Register  to view this content.

[gmr4evr1]

You're welcome, glad its working for you.