Filling a ComboBox?

[CG Rosén]

Good Day Group,

Need some hints on the following;

With the FindMethod, the Range ("A1:A10000") is scanned to find the cells
with the word "XXX". I like
the found values in the corresponding column "B" to be
stored as text in a ComboBox. What is the best method for this??

Brgds

CG Rosén

 

[Dave Peterson]

One way:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim FoundCell As Range
Dim FirstAddress As String

Set wks = Worksheets("Sheet1")
wks.OLEObjects("ComboBox1").Object.Clear

With wks.Range("a1:a10000")

Set FoundCell = .Cells.Find(what:="xxx", after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlPart, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)

If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
wks.OLEObjects("ComboBox1").Object.AddItem _
FoundCell.Offset(0, 1).Value

Set FoundCell = .FindNext(FoundCell)

Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With

End Sub

Most of this was stolen from the example for .Find in VBA's help.