类型不匹配错误 Excel VBA - 循环遍历已用范围的每一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19387985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Type Mismatch Error Excel VBA - Looping through each column of Used Range
提问by H3lue
I have an excel spreadsheet with a title row at the top. I am trying to log the column number of each cell in the top row of my excel spreadsheet (title row) that matches a certain string such as "Recommended Action 1"
我有一个 Excel 电子表格，顶部有一个标题行。我试图在我的 excel 电子表格（标题行）的顶行中记录每个单元格的列号，该列与某个字符串匹配，例如“推荐的操作 1”
For Each c In Worksheets("Cost Estimates").Range(Cells(1, 1), Cells(totalRows, totalCols))
If c.Value = "Recommended Action 1" Then
recAct1 = c.Column
Debug.Print CStr(recAct1)
ElseIf c.Value = "Recommended Action 2" Then
recAct2 = c.Column
ElseIf c.Value = "Recommended Action 3" Then
recAct3 = c.Column
End If
Next
Where recAct holds the column number and totalRows and totalCols are the total number of rows and columns (respectively on the spreadsheet).
其中 recAct 保存列号，totalRows 和 totalCols 是行和列的总数（分别在电子表格上）。
I keep receiving a 'Type Mismatch' error for:
我不断收到“类型不匹配”错误：
If c.Value = "Recommended Action 1" Then
I put my cursor over the c value during this error and I get an 'Error 2023' message.
在此错误期间，我将光标放在 c 值上，并收到“错误 2023”消息。
I suspected that it was because c was a column number and not an actual range address. I think this error is caused by the fact that I do not know what type of variable 'c' is actually returning -- I thought it was a range object.
我怀疑这是因为 c 是列号而不是实际的范围地址。我认为这个错误是由于我不知道实际上返回的是什么类型的变量“c”——我认为它是一个范围对象。
采纳答案by Siddharth Rout
I think this is what you are trying?
我认为这就是你正在尝试的？
Option Explicit
Sub Build_Formulas_v2()
Dim RAOneCol As Long, RATwoCol As Long, RAThreeCol As Long
RAOneCol = GetCol("Recommended Action 1")
RATwoCol = GetCol("Recommended Action 2")
RAThreeCol = GetCol("Recommended Action 3")
If RAOneCol <> 0 Then _
MsgBox "Recommended Action 1 found in column " & RAOneCol Else _
MsgBox "Recommended Action 1 not found"
If RATwoCol <> 0 Then _
MsgBox "Recommended Action 2 found in column " & RATwoCol Else _
MsgBox "Recommended Action 2 not found"
If RAThreeCol <> 0 Then _
MsgBox "Recommended Action 3 found in column " & RAThreeCol Else _
MsgBox "Recommended Action 3 not found"
End Sub
Function GetCol(sString As String) As Long
Dim aCell As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Cost Estimates")
Set aCell = ws.Rows(1).Find(What:=sString, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
GetCol = aCell.Column
End If
End Function
