如何获得 vba 循环结果以填充组合框? [英] How do I get vba loop result to populate a combobox?
问题描述
问题:我需要在活动工作簿中搜索工作表列表,并返回每个工作表的名称,该工作表的单元格的值与搜索输入匹配.然后,这些工作表的名称需要使用重复项填充用户表单组合框.
Problem: I need to search a list of worksheets in the active workbook and return the name of every worksheet that has a cell whose value matches a search input. The names of these worksheets need to then populate a userform combobox with duplicates.
部分解决方案:我已经能够对执行上述大部分任务的一段代码进行逆向工程.但是,工作表名称当前填充了重复的 msgbox.我如何让这个结果填充一个组合框?
Partial Solution: I've been able to reverse-engineer a piece of code that does most of the above. However, the worksheet names currently populate a msgbox with duplication. How would I make this result populate a combobox instead?
我一直在尝试输出到集合以及将结果写入新的工作表,但这些选项仍处于概念阶段,因此我没有要发布的代码.
I've been experimenting with outputting to a collection as well as writing results to a new worksheet, but these option are still in the conceptual phase, so I have no code to post.
更新(一些代码):
Public Sub FindDate()
'find date data on all sheets
Dim ws As Worksheet
Dim rngFind As Range
Dim myDate As String
Dim firstAddress As String
Dim addressStr As String
Dim findNum As Integer
Dim sheetArray(299) As Integer
Dim arrayIndex As Integer
myDate = InputBox("Enter date to find")
If myDate = "" Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
'Do not search the following sheets
With ws
If ws.Name = "CM Chapters" Then GoTo myNext
If ws.Name = "CM Codes" Then GoTo myNext
If ws.Name = "PCS Categories" Then GoTo myNext
If ws.Name = "PCS Chapters" Then GoTo myNext
If ws.Name = "PCS Code" Then GoTo myNext
Set rngFind = .Columns(41).Find(what:=myDate, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not rngFind Is Nothing Then
firstAddress = rngFind.Address
Do
findNum = findNum + 1
addressStr = addressStr & .Name & vbCrLf
''''Original working code
' addressStr = addressStr & .Name & " " & rngFind.Address & vbCrLf
''''Modified to remove excess text
Set rngFind = .Columns(41).FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> firstAddress
End If
myNext:
End With
Next ws
If Len(addressStr) Then
'''' Original working code
' MsgBox "Found: "" & myDate & "" " & findNum & " times." & vbCr & _
' addressStr, vbOKOnly, myDate & " found in these cells"
'''' Modified to to remove excess text
MsgBox vbCr & addressStr
Else:
MsgBox "Unable to find " & myDate & " in this workbook.", vbExclamation
End If
End Sub
推荐答案
试试这个
Do
findNum = findNum + 1
addressStr = addressStr & .Name
ComboBox1.AddItem addressStr 'replace ComboBox1 with your ComboBox name
addressStr = addressStr & vbCrLf ' if you still want to add the Line feed
Set rngFind = .Columns(41).FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> firstAddress
这篇关于如何获得 vba 循环结果以填充组合框?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!