VBA Excel VLOOKUP

tommy46

New Member
Joined
Mar 14, 2019
Messages
2
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

Code:
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.


Code:
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,746
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
NamedateTextCat1Cat2Value
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
NamedateTextCat1Cat2Value
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,985
Messages
5,526,063
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top