Good morning,
早上好,
I am trying to find a way to:
我正试图找到一种方法:
- Loop through a column (B column)
- 循环通过一列(B列)
- Take the values, store them in an array
- 获取值,将它们存储在一个数组中
- Loop through that array and do some text manipulation
- 循环遍历该数组并进行一些文本操作
However, I cannot think of a way to loop through a column and take those values, storing them in an array. I have looked through Stack Overflow and google but have not found a successful solution (yet).
但是,我想不出一种循环遍历列并获取这些值的方法,将它们存储在数组中。我已经浏览了Stack Overflow和谷歌,但还没有找到一个成功的解决方案(还)。
In advance, thank you for your help.
提前谢谢你的帮助。
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
2 个解决方案
#1
2
Just adding a variation on Vityata's which is the simplest way. This method will only add non-blank values to your array. When using your method you must declare the size of the array using Redim.
只需在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
#2
2
This is the easiest way to get column to array:
这是将列添加到数组的最简单方法:
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
It takes the values from B1
to B10
in array and it gives possibility to add "something" to this array.
它采用数组中B1到B10的值,它可以为这个数组添加“某些东西”。
The Transpose()
function takes the single column range and stores it as an array with one dimension. If the array was on a single row, then you would have needed a double transpose, to make it a single dimension array:
Transpose()函数采用单列范围并将其存储为具有一个维度的数组。如果数组在一行上,那么你需要一个双转置,使它成为一个单维数组:
With Application
myArray = .Transpose(.Transpose(Range("A1:K1")))
End With
-
MSDN转置
-
CPearson Range To Array
-
Creating an Array from a Range in VBA
从VBA中的范围创建数组
#1
2
Just adding a variation on Vityata's which is the simplest way. This method will only add non-blank values to your array. When using your method you must declare the size of the array using Redim.
只需在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
#2
2
This is the easiest way to get column to array:
这是将列添加到数组的最简单方法:
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
It takes the values from B1
to B10
in array and it gives possibility to add "something" to this array.
它采用数组中B1到B10的值,它可以为这个数组添加“某些东西”。
The Transpose()
function takes the single column range and stores it as an array with one dimension. If the array was on a single row, then you would have needed a double transpose, to make it a single dimension array:
Transpose()函数采用单列范围并将其存储为具有一个维度的数组。如果数组在一行上,那么你需要一个双转置,使它成为一个单维数组:
With Application
myArray = .Transpose(.Transpose(Range("A1:K1")))
End With
-
MSDN转置
-
CPearson Range To Array
-
Creating an Array from a Range in VBA
从VBA中的范围创建数组