我在同一工作簿中有多个格式相同的Excel文件。在每个文件中,我都有从a2开始的日期:a416和I在b1、d1、f1、h1、j1、l1、n1、p1、r1、t1、v1和x1处有数字作为标题。在用户表单中,我有两个列表框,一个包含标题为ListBoxColumns的日期,另一个包含标题为ListBoxColumns的数字。我试图编写一段代码,在我按下一个按钮后,它从ListBoxColumns的ListBox中获取值并选择匹配的行,然后从ListBoxHeaders中获取值并选择匹配的列,然后选择行和列相交的单元格。例如,如果日期11/29/23在单元格a18中,数字0.00814是列D的标题,我需要它来最终选择单元格d18。
Sub FindColumn()
Dim selectedValue As String
Dim headerRange As Range
Dim headerCell As Range
Dim targetColumn As Long
' Get the selected value from the list box
selectedValue = ListBoxHeaders.Value
' Set the range of headers (assuming headers are in the first row)
Set headerRange = activeSheet.Rows(1)
' Loop through each cell in the header range
For Each headerCell In headerRange
' Check if the header matches the selected value
If headerCell.Value = selectedValue Then 'error on this line
' Get the column number of the matching header
targetColumn = headerCell.Column
' Exit the loop since we found the match
Exit For
End If
Next headerCell
' Display the column number
MsgBox "The column number for " & selectedValue & " is: " & targetColumn
End Sub
字符串
我想开始只是选择列让我的脚湿,但它不工作。它给出了一个错误代码的类型不匹配的粗体行。任何帮助将不胜感激。
1条答案
按热度按时间nmpmafwu1#
当你想要精确匹配时,使用
Application.Match
更简单:字符串