VBA Excel VLOOKUP

[tommy46]

Hi Guys,


Basically I have a combo box on a userform which Is populated using the following code.

The below is done on userform initialize

Me.ComboBox1.List = ActiveSheet.ListObjects("Table1").ListColumns(1).DataBodyRange.Value

Once ComboBox Changes on user selection the following code does a VLOOKUP Based on the Value in the combo box and finds the data in the desired column and then places it in the textbox.

Me.TextBox1.Text = Application.WorksheetFunction.VLOOKUP(me.ComboBox1.Value, Worksheets("Sheet1").Range ("A2:W591") , 6 , False)

Supposing my data is filtered on the sheet how would I tell it to only vlookup the visible cells?

Also I Would like to add another textbox and button that would allow me to click to vlookup like the above but then replace the contents of the lookup with whats in the textbox. I would also like to add that the value in the combo box is always unique

cheers

 

[Yongle]

Dealing only with your first question
- here is a method to lookup visible cells
- in example looking for "s" in visible cells in column E and returning value in column F (using offset)
- example returns 500

Sub FindFirstMatchedVisibleCell()
    Dim rng As Range, cel As Range, findWhat As String
    findWhat = "s"
    Set rng = Range("E:E").SpecialCells(xlCellTypeVisible)
    On Error Resume Next
    Set cel = rng.Find(findWhat)
    MsgBox cel.Offset(, 1).Value
End Sub

BEFORE FILTER
Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​
1​	Name	date	Text	Cat1	Cat2	Value
2​	Name01	13/03/2019​	text403	1​	s	100​
3​	Name02	13/03/2019​	text547	1​	d	200​
4​	Name03	14/03/2019​	text516	1​	f	300​
5​	Name04	14/03/2019​	text181	1​	g	400​
6​	Name05	13/03/2019​	text912	2​	s	500​
7​	Name06	11/03/2019​	text435	2​	d	600​
8​	Name07	12/03/2019​	text580	2​	f	700​
9​	Name08	13/03/2019​	text405	2​	g	800​
10​	Name09	13/03/2019​	text548	3​	s	900​
11​	Name10	13/03/2019​	text564	3​	d	1000​
12​	Name11	14/03/2019​	text116	3​	f	1100​
13​	Name12	14/03/2019​	text158	3​	g	1200​

Sheet: Sheet1

AFTER FILTER

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​
1​	Name	date	Text	Cat1	Cat2	Value
2​	Name05	43536​	text892	2​	s	500​
3​	Name06	43535​	text647	2​	d	600​
4​	Name07	43536​	text723	2​	f	700​
5​	Name08	43536​	text888	2​	g	800​

Sheet: Sheet2

 

[Yongle]

I was lazy but you may need to be more selective when using Range.Find
Have a look at this for all the parameters available to get only the match that you want
https://docs.microsoft.com/en-us/office/vba/api/excel.range.find