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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Name[/td][td]date[/td][td]Text[/td][td]Cat1[/td][td]Cat2[/td][td]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Name01[/td][td]

13/03/2019​

[/td][td]text403[/td][td]

1​

[/td][td]s[/td][td]

100​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Name02[/td][td]

13/03/2019​

[/td][td]text547[/td][td]

1​

[/td][td]d[/td][td]

200​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Name03[/td][td]

14/03/2019​

[/td][td]text516[/td][td]

1​

[/td][td]f[/td][td]

300​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Name04[/td][td]

14/03/2019​

[/td][td]text181[/td][td]

1​

[/td][td]g[/td][td]

400​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Name05[/td][td]

13/03/2019​

[/td][td]text912[/td][td]

2​

[/td][td]s[/td][td]

500​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Name06[/td][td]

11/03/2019​

[/td][td]text435[/td][td]

2​

[/td][td]d[/td][td]

600​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Name07[/td][td]

12/03/2019​

[/td][td]text580[/td][td]

2​

[/td][td]f[/td][td]

700​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Name08[/td][td]

13/03/2019​

[/td][td]text405[/td][td]

2​

[/td][td]g[/td][td]

800​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]Name09[/td][td]

13/03/2019​

[/td][td]text548[/td][td]

3​

[/td][td]s[/td][td]

900​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]Name10[/td][td]

13/03/2019​

[/td][td]text564[/td][td]

3​

[/td][td]d[/td][td]

1000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]Name11[/td][td]

14/03/2019​

[/td][td]text116[/td][td]

3​

[/td][td]f[/td][td]

1100​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]Name12[/td][td]

14/03/2019​

[/td][td]text158[/td][td]

3​

[/td][td]g[/td][td]

1200​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

AFTER FILTER

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Name[/td][td]date[/td][td]Text[/td][td]Cat1[/td][td]Cat2[/td][td]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Name05[/td][td]

43536​

[/td][td]text892[/td][td]

2​

[/td][td]s[/td][td=bgcolor:#FF0000]

500​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Name06[/td][td]

43535​

[/td][td]text647[/td][td]

2​

[/td][td]d[/td][td]

600​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Name07[/td][td]

43536​

[/td][td]text723[/td][td]

2​

[/td][td]f[/td][td]

700​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Name08[/td][td]

43536​

[/td][td]text888[/td][td]

2​

[/td][td]g[/td][td]

800​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

 

[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