列上的 Excel VBA 循环

Excel VBA Loop on columns

提问by wahyueka31

when we are going to do a loop in the rows, we can use code like the following:

当我们要在行中进行循环时，我们可以使用如下代码：

i = 1
Do
   Range("E" & i & ":D" & i).Select
   i = i + 1
Loop Until i > 10

but what if we want to do a loop on a column?

但是如果我们想在一个列上做一个循环呢？

Can we use the same method as above?

我们可以使用与上面相同的方法吗？

while the columns in Excel is a complex such as A, B, C, ..., Y, Z, AA, AB, AC, ..., etc. problems will arise between loop from the "Z" to the "AA".

而Excel中的列是一个复杂的，例如A，B，C，...，Y，Z，AA，AB，AC，...等从“Z”到“AA”的循环之间会出现问题”。

how we do looping alphabet column from "A" to "Z" and then continued into "AA", "AB" and so on

我们如何将字母列从“A”循环到“Z”，然后继续到“AA”、“AB”等

is there anything that can help?

有什么可以帮助的吗？

回答by Larry

Yes, let's use Selectas an example

是的，让我们Select举个例子

sample code: Columns("A").select

示例代码： Columns("A").select

How to loop through Columns:

如何遍历列：

Method 1: (You can use index to replace the Excel Address)

方法一：（可以用索引代替Excel地址）

For i = 1 to 100
    Columns(i).Select
next i

Method 2: (Using the address)

方法二：（使用地址）

For i = 1 To 100
 Columns(Columns(i).Address).Select
Next i

EDIT: Strip the Column for OP

编辑：剥离 OP 列

columnString = Replace(Split(Columns(27).Address, ":")(0), "$", "")

e.g. you want to get the 27th Column --> AA, you can get it this way

例如你想得到第 27 列 --> AA，你可以这样得到

回答by bonCodigo

Another method to try out. Also selectcould be replaced when you set the initial column into a Range object. Performance wise it helps.

另一种方法可以尝试。select当您将初始列设置为 Range 对象时，也可以替换。性能明智它有帮助。

Dim rng as Range

Set rng = WorkSheets(1).Range("A1") '-- you may change the sheet name according to yours.

'-- here is your loop
i = 1
Do
   '-- do something: e.g. show the address of the column that you are currently in
   Msgbox rng.offset(0,i).Address 
   i = i + 1
Loop Until i > 10

** Two methods to get the column name using column number**

**使用列号获取列名的两种方法**

• Split()

• 分裂（）

code

代码

colName = Split(Range.Offset(0,i).Address, "$")(1)

• String manipulation:

• 字符串操作：

code

代码

Function myColName(colNum as Long) as String
    myColName = Left(Range(0, colNum).Address(False, False), _ 
    1 - (colNum > 10)) 
End Function 

回答by whytheq

If you want to stick with the same sort of loop then this will work:

如果您想坚持使用相同类型的循环，那么这将起作用：

Option Explicit

Sub selectColumns()

Dim topSelection As Integer
Dim endSelection As Integer
topSelection = 2
endSelection = 10

Dim columnSelected As Integer
columnSelected = 1
Do
   With Excel.ThisWorkbook.ActiveSheet
        .Range(.Cells(columnSelected, columnSelected), .Cells(endSelection, columnSelected)).Select
   End With
   columnSelected = columnSelected + 1
Loop Until columnSelected > 10

End Sub

EDIT

编辑

If in reality you just want to loop through every cell in an area of the spreadsheet then use something like this:

如果实际上您只想遍历电子表格区域中的每个单元格，请使用以下内容：

Sub loopThroughCells()

'=============
'this is the starting point
Dim rwMin As Integer
Dim colMin As Integer
rwMin = 2
colMin = 2
'=============

'=============
'this is the ending point
Dim rwMax As Integer
Dim colMax As Integer
rwMax = 10
colMax = 5
'=============

'=============
'iterator
Dim rwIndex As Integer
Dim colIndex As Integer
'=============

For rwIndex = rwMin To rwMax
        For colIndex = colMin To colMax
            Cells(rwIndex, colIndex).Select
        Next colIndex
Next rwIndex

End Sub