我找到了将数字转换为列字母的代码。
我该如何将列字母转换为数字?
Sub colLtr()
Dim mycolumn
mycolumn = 1000
Mcl = Left(Cells(1, mycolumn).Address(1, 0), InStr(1, Cells(1, mycolumn).Address(1, 0), "$") - 1)
MsgBox Mcl
End Sub
我找到了将数字转换为列字母的代码。
我该如何将列字母转换为数字?
Sub colLtr()
Dim mycolumn
mycolumn = 1000
Mcl = Left(Cells(1, mycolumn).Address(1, 0), InStr(1, Cells(1, mycolumn).Address(1, 0), "$") - 1)
MsgBox Mcl
End Sub
您可以按其字母引用列,如下所示:
Columns("A")
所以要获得列号,只需像这样修改上述代码:
Columns("A").Column
上面的代码会返回一个整数(在这种情况下为1)。
因此,如果您正在使用变量mycolumn
来存储和引用列号,您可以这样设置值:
mycolumn = Sheets("Sheet1").Columns("A").Column
然后,您可以通过以下方式引用变量:
Sheets("Sheet1").Columns(mycolumn)
或引用单元格 (A1
):
Sheets("Sheet1").Cells(1,mycolumn)
或者要引用一系列单元格(A1:A10
),你可以使用:
Sheets("Sheet1").Range(Cells(1,mycolumn),Cells(10,mycolumn))
.cells
而不是 cells
。注意点号“.”。 - Timo?Columns("A").Column
,按回车键,您应该会得到输出1
。 - undefinedFunction getColIndex(sColRef As String) As Long
Dim sum As Long, iRefLen As Long
sum = 0: iRefLen = Len(sColRef)
For i = iRefLen To 1 Step -1
sum = sum + Base26(Mid(sColRef, i)) * 26 ^ (iRefLen - i)
Next
getColIndex = sum
End Function
Private Function Base26(sLetter As String) As Long
Base26 = Asc(UCase(sLetter)) - 64
End Function
一些例子:
getColIndex("A") '-->1
getColIndex("Z") '-->26
getColIndex("AA") '-->27
getColIndex("AZ") '-->52
getColIndex("AAA") '-->703
ARich提供了一个很好的解决方案,并展示了我使用了一段时间的方法,但Sancarn是正确的,它不是最优的。它速度有点慢,如果输入错误会导致错误,并且不太健壮。Sancarn走在正确的轨道上,但缺少一些错误检查:例如,getColIndex("_")和getColIndex("AE")都将返回31。其他非字母字符(例如:“*”)有时会返回各种负值。
这是我编写的一个函数,将把列字母转换为数字。如果输入不是工作表上的列,则会返回-1(除非AllowOverflow设置为TRUE)。
Function ColLetter2Num(ColumnLetter As String, Optional AllowOverflow As Boolean) As Double
'Converts a column letter to a number (ex: C to 3, A to 1, etc). Returns -1 if its invalid.
' @ColumnLetter - the letter(s) to convert to a number.
' @AllowOverflow - if TRUE, can return a number greater than the max columns.
On Error GoTo invalidCol
If Len(ColumnLetter) = 0 Then GoTo invalidCol
Dim thisChar As String
For i = 1 To Len(ColumnLetter) 'for each character in input
thisChar = Mid(ColumnLetter, i, 1) 'get next character
If Asc(UCase(thisChar)) >= 65 And Asc(UCase(thisChar)) <= 90 Then 'if the character is a letter
ColLetter2Num = ColLetter2Num + (26 ^ (Len(ColumnLetter) - i)) * (Asc(UCase(thisChar)) - 64) 'add its value to the return
Else
GoTo invalidCol 'if the character is not a letter, return an error
End If
If AllowOverflow = False And (ColLetter2Num = 0 Or ColLetter2Num > Columns.Count) Then
'if the value is not inside the bounds of the sheet, return an error and stop
invalidCol:
ColLetter2Num = -1 'error
Exit Function 'stop checking
End If
Next i
End Function
Sub test()
Debug.Print ColLetter2Num("A") 'returns 1
Debug.Print ColLetter2Num("IV") 'returns 256 (max columns for excel 2003 and prior))
Debug.Print ColLetter2Num("XFD") 'returns -1 (invalid because IV is the last column for .xls workbooks)
Debug.Print ColLetter2Num("XFD", True) 'returns 16384 (does not return -1 because AllowOverflow = TRUE)
Debug.Print ColLetter2Num("A_", True) 'returns -1 (invalid because "_" is not a column)
Debug.Print ColLetter2Num("132", True) 'returns -1 (invalid because "1" is not a column)
If ColLetter2Num("A") <> -1 Then
Debug.Print "The input is a valid column on the sheet."
Else
Debug.Print "The input is NOT a valid column on the sheet."
End If
End Sub
要查看字母指定的列的数字等效项:
Sub dural()
ltrs = "ABC"
MsgBox Cells(1, ltrs).Column
End Sub