Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Loop through column, store values in an array

Tags:

excel

vba

I am trying to find a way to:

  1. Loop through a column (B column)
  2. Take the values, store them in an array
  3. 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.

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
like image 616
artemis Avatar asked Dec 18 '17 13:12

artemis


People also ask

How do you loop through an array in Excel?

In VBA, to loop through an array you can use the For Loop (For Next). And to write this loop code you need to know the size of the array (upper and lower bound) so that you can use both as the counter for the loop. Basically, for loop will start from the first element of the array and loop up to the last.

How do you loop through a column in VBA Excel?

To loop through a range in VBA, you need to use the “For Each Loop”. By using this loop, you can write a code where you tell VBA to go through each cell in the range, column, or row and perform a specific activity.


2 Answers

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.

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:

With Application
    myArray = .Transpose(.Transpose(Range("A1:K1")))
End With
  • MSDN Transpose

  • CPearson Range To Array

  • Creating an Array from a Range in VBA

like image 50
Vityata Avatar answered Oct 07 '22 01:10

Vityata


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.

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
like image 3
SJR Avatar answered Oct 06 '22 23:10

SJR