遍历列，

[weixin_38065111]

早上好， 我试图找到一种方式来存储值的数组：通过一列 回路（B柱） 取值，商店他们在一个数组 遍历数组，并做一些文字处理 但是，我不能想办法来循环THROU gh列并将这些值存储在一个数组中。我已经通过堆栈溢出和谷歌，但还没有找到一个成功的解决方案（还）。 提前，谢谢你的帮助。 Sub collectNums() Dim eNumStorage() As String ' initial storage array to take values Dim i as Integer Dim j as Integer Dim lrow As Integer lrow = Cells(Rows.Count, "B").End(xlUp).Row ' The amount of stuff in the column For i = lrow To 2 Step -1 If (Not IsEmpty(Cells(i, 2).Value)) Then ' checks to make sure the value isn't empty i = eNumStorage ' I know this isn't right Next i If (IsEmpty(eNumStorage)) Then MsgBox ("You did not enter an employee number for which to query our database. Quitting") Exit Sub End If End Sub

[weixin_38076769]

这是让列阵列的最简单的方法： Public Sub TestMe() Dim myArray As Variant Dim cnt As Long myArray = Application.Transpose(Range("B1:B10")) For cnt = LBound(myArray) To UBound(myArray) myArray(cnt) = myArray(cnt) & "something" Next cnt For cnt = LBound(myArray) To UBound(myArray) Debug.Print myArray(cnt) Next cnt End Sub 它从B1值来B10阵列，它给可能性“东西”添加到这个阵列。 Transpose()函数采用单列范围并将其作为一维数组存储。如果数组是在同一行上，那么你会需要一个双转置，使之成为一维数组： With Application myArray = .Transpose(.Transpose(Range("A1:K1"))) End With MSDN Transpose CPearson Range To Array Creating an Array from a Range in VBA

[weixin_38065957]

Ju在Vityata上添加一个变化是最简单的方法。此方法只会将非空值添加到您的数组中。在使用你的方法时，你必须用Redim声明数组的大小。 Sub collectNums() Dim eNumStorage() As String ' initial storage array to take values Dim i As Long Dim j As Long Dim lrow As Long lrow = Cells(Rows.Count, "B").End(xlUp).Row ' The amount of stuff in the column ReDim eNumStorage(1 To lrow - 1) For i = lrow To 2 Step -1 If (Not IsEmpty(Cells(i, 2).Value)) Then ' checks to make sure the value isn't empty j = j + 1 eNumStorage(j) = Cells(i, 2).Value End If Next i ReDim Preserve eNumStorage(1 To j) 'Not sure what this bit is doing so have left as is If (IsEmpty(eNumStorage)) Then MsgBox ("You did not enter an employee number for which to query our database. Quitting") Exit Sub End If For j = LBound(eNumStorage) To UBound(eNumStorage) ' loop through the previous array eNumStorage(j) = Replace(eNumStorage(j), " ", "") eNumStorage(j) = Replace(eNumStorage(j), ",", "") Next j End Sub