Find and select cell to match value from combobox

picklefactory

Active Member
Joined
Jan 28, 2005
Messages
398
Hello folks
Stumped again.... :(
I want to find and select a cell via VBA based on a text value picked from a userform combobox. The target value will be a text value in a single column which will NOT be sorted and may contain blanks. Is that doable at all?
Thanks
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,710
Office Version
2007
Platform
Windows
Try this

Change data in red for your information

Code:
Private Sub ComboBox1_Change()
  Dim sh As Worksheet, f As Range
  If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
  Set sh = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
  Set f = sh.Range("[COLOR=#ff0000]A:A[/COLOR]").Find(ComboBox1.Value, , xlValues, [COLOR=#0000ff]xlWhole[/COLOR])
  If Not f Is Nothing Then
    sh.Select
    f.Select
  Else
    MsgBox "Does not exist"
  End If
End Sub
note: If you want to find part of a text inside the cell, change XlWhole to XlPart
 

picklefactory

Active Member
Joined
Jan 28, 2005
Messages
398
It's really frustrating how easy you guys make it look....... works like a charm, perfect. Thank you very much for your time and trouble, bailed me out again.
:)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,710
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,085,882
Messages
5,386,536
Members
402,004
Latest member
moneyman4438

Some videos you may like

This Week's Hot Topics

Top