如何在 vba 中使用列号而不是字母创建范围?
要引用单元格范围,可以使用Range(Cell1,Cell2),例如:
Sub RangeTest()
Dim testRange As Range
Dim targetWorksheet As Worksheet
Set targetWorksheet = Worksheets("MySheetName")
With targetWorksheet
.Cells(5, 10).Select 'selects cell J5 on targetWorksheet
Set testRange = .Range(.Cells(5, 5), .Cells(10, 10))
End With
testRange.Select 'selects range of cells E5:J10 on targetWorksheet
End Sub
以下是选择A1范围的两种解决方案。
Cells(1,1).Select '(row 1, column 1)
Range("A1").Select
同时请查看此链接;
我们强烈建议您使用Range而不是Cells来处理单元格和单元格组。这样可以使您的语句更清晰,而且您不必记住AE列是第31列。
唯一使用Cells的时间是当您想要选择整个工作表的所有单元格时。例如:Cells.Select 选择所有单元格,然后清空所有单元格的值或公式,您将使用:Cells.ClearContents
--
“Cells”在动态设置范围并通过计数器循环遍历范围时特别有用。使用字母作为列号定义范围可能在短期内更透明,但它也会使您的应用程序更加僵硬,因为它们是“硬编码”的表示方式,而不是动态的。
是的!您可以使用Range.EntireColumn
MSDN
dim column : column = 4
dim column_range : set column_range = Sheets(1).Cells(column).EntireColumn
如果你想要访问某一列,你可以使用硬编码的列区间语法,例如 Range("D:D")
。
然而,我建议使用整个列范围,因为它提供了更多灵活性,可以在以后更改该列。
Worksheet.Columns
提供对工作表中某一列的区域访问。 MSDN
如果您想要访问第一个工作表的第一列,则需要在工作表上调用 Columns
函数。
dim column_range: set column_range = Sheets(1).Columns(1)
Columns
属性也适用于任何Range
MSDN
如果您有单个单元格的范围,但想要访问行上的其他单元格,类似于LOOKUP
,那么EntireRow
也可能有用。
dim id : id = 12345
dim found : set found = Range("A:A").Find(id)
if not found is Nothing then
'Get the fourth cell from the match
MsgBox found.EntireRow.Cells(4)
end if
以下是一种简化的ConvertToLetter函数替代方案,理论上适用于所有正整数。例如,1412将产生"BBH"作为结果。
Public Function ColumnNumToStr(ColNum As Integer) As String
Dim Value As Integer
Dim Rtn As String
Rtn = ""
Value = ColNum - 1
While Value > 25
Rtn = Chr(65 + (Value Mod 26)) & Rtn
Value = Fix(Value / 26) - 1
Wend
Rtn = Chr(65 + Value) & Rtn
ColumnNumToStr = Rtn
End Function
如果您需要将列数转换为字母:
Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function
这样你就可以做类似以下的事情:
Function selectColumnRange(colNum As Integer, targetWorksheet As Worksheet)
Dim colLetter As String
Dim testRange As Range
colLetter = ConvertToLetter(colNum)
testRange = targetWorksheet.Range(colLetter & ":" & colLetter).Select
End Function
For intloop = 1 To 26
colcheck(intloop) = Chr$(64 + intloop)
For lenloop = 1 To 26
colcheck((intloop * 26) + lenloop) = Chr$(64 + intloop) & Chr$(64 + lenloop)
For terloop = 1 To 26
colcheck((intloop * 676) + (lenloop * 26) + terloop) = Chr$(64 + intloop) & Chr$(64 + lenloop) & Chr$(64 + terloop)
For qualoop = 1 To 26
colcheck((intloop * 17576) + (lenloop * 676) + (terloop * 26) + qualoop) = Chr$(64 + intloop) & Chr$(64 + lenloop) & Chr$(64 + terloop) & Chr$(64 + qualoop)
Next qualoop
Next terloop
Next lenloop
Next intloop
然后只需使用colcheck(您的列号)即可获取与该字母相关联的列标题(即colcheck(703) = AAA)。
Function ConvertToLetter2(iCol As Integer) As String
Dim First As Integer
Dim Second As Integer
Dim FirstChar As String
Dim SecondChar As String
First = Int(iCol / 26)
If First = iCol / 26 Then
First = First - 1
End If
If First = 0 Then
FirstChar = ""
Else
FirstChar = Chr(First + 64)
End If
Second = iCol Mod 26
If Second = 0 Then
SecondChar = Chr(26 + 64)
Else
SecondChar = Chr(Second + 64)
End If
ConvertToLetter2 = FirstChar & SecondChar
End Function
public static String ConvertToLiteral(int number)
{
int firstLetter = (((number - 27) / (26 * 26))) % 26;
int middleLetter = ((((number - 1) / 26)) % 26);
int lastLetter = (number % 26);
firstLetter = firstLetter == 0 ? 26 : firstLetter;
middleLetter = middleLetter == 0 ? 26 : middleLetter;
lastLetter = lastLetter == 0 ? 26 : lastLetter;
String returnedString = "";
returnedString = number > 27 * 26 ? (Convert.ToChar(firstLetter + 64).ToString()) : returnedString;
returnedString += number > 26 ? (Convert.ToChar(middleLetter + 64).ToString()) : returnedString;
returnedString += lastLetter >= 0 ? (Convert.ToChar(lastLetter + 64).ToString()) : returnedString;
return returnedString;
}
Function fncToLetters(vintCol As Integer) As String
Dim mstrDigits As String
' Convert a positive number n to its digit representation in base 26.
mstrDigits = ""
Do While vintCol > 0
mstrDigits = Chr(((vintCol - 1) Mod 26) + 65) & mstrDigits
vintCol = Int((vintCol - 1) / 26)
Loop
fncToLetters = mstrDigits
End Function
如果你不知道最后一行或列是什么,但仍想获取范围,请使用
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
LastColumn = ActiveSheet.Cells(7, ActiveSheet.Columns.Count).End(xlToLeft).Column
'Column Transform number in Letter
Col_Letter = Split(Cells(1, LastColumn).Address(True, False), "$")(0)
x_range = "A1:"
y_range = Col_Letter & Trim(Str(LastRow))
'Set the range
rng_populated = x_range & "" & y_range
'Select the range
Range(rng_populated).Select
Cells(1, "A")
:) - JMaxRange("A1")
=Cells(1,1)
)。 - CustomX