CODEWITHSUNDEEP
arraysvbaexcel
artemis
artemis

Reputation: 7241

VBA copy values from one array and store them into another

I am hoping to find help with this current VBA problem. I have looked throughout Stack Overflow and other Google searches, but can't seem to find what I'm looking for.

Essentially, I have a user pasted value on my page which I am delimiting on a comma, and then storing that into an array. What I am aiming to do is then loop through that array and eliminate any extra spaces, and then also delete any values that AREN'T a number.

  1. Copy user values
  2. Store into an array
  3. Erase whitespace

So far, I have not been able to:

  1. Copy items that ARE a number to new array

Currently, my code looks like:

Sub grabText()

' This macro was written as a test macro to grab and filter data entered in a textbox

    Application.ScreenUpdating = False

    Dim enteredValue As String ' Value taken from page
    Dim vals() As String ' Array once it is split
    Dim goodvals() As String 'Formatted array
    Dim i As Integer 'Index

    enteredValue = ActiveSheet.myTxt.Text
   ' MsgBox enteredValue

    vals() = Split(enteredValue, ",")

    lastitem = UBound(vals)
   ' MsgBox lastitem

    'Formats array
    For i = LBound(vals) To UBound(vals)
        i = TRIM(vals(i))
        '
        '   If (ISNUMBER(vals(i)) == TRUE) Then
        '       enter i into goodvals()
        '
    Next i

    Application.ScreenUpdating = True

Any help or advice would be greatly appreciated. I was thinking about ways to do this in other languages (Java, Python) and I was thinking about Linked-Lists.

Thanks in advance!

Upvotes: 0

Views: 5430

Answers (1)

trincot
trincot

Reputation: 349946

Some issues:

  • Don't assign the result of Split to vals(), but to vals
  • Don't re-use the variable i for the result of the Trim. It is better to use a separate variable for that, which you can then type as a String

You can capture the desired result if you

  • First reserve enough room for your target array: it can never be longer than the Split result, so use that as the initial size
  • Use a separate index variable for referencing the target array index, and only increment it when you have stored a number in it
  • Finally reduce the size of the target array to the size that was actually used

Code:

Dim enteredValue As String ' Value taken from page
Dim vals() As String ' Array once it is split
Dim goodvals() As String  'Formatted array
Dim i As Long 'Index in vals
Dim j As Long 'Index in goodvals
Dim s As String 'Individual string

enteredValue = ActiveSheet.myTxt.Text

vals = Split(enteredValue, ",")
' Reserve as many entries in the target array
ReDim goodvals(UBound(vals))

j = LBound(goodvals)
For i = LBound(vals) To UBound(vals)
    s = Trim(vals(i))
    If IsNumeric(s) Then 
        goodvals(j) = CDbl(s)
        MsgBox goodvals(j)
        j = j + 1
    End If
Next

' Shorten the array size to the part that is used
If j Then
    ReDim Preserve goodvals(j - 1)
Else ' There were no numericals at all, so erase the array:
    Erase goodvals
End If

Upvotes: 3

Related Questions