Use array to search for CASE values Excel VBA

Question

I'm trying to create an array from a list of variable values within one cell.

For example: Cell B3 contains values: 1A, 2B, 3A, 4A, 5C.

I want to convert that data so that: myarray=("1A", "2B", "3A", "4A", "5C")

i.e.

 myarray(1) = "1A"
 myarray(2) = "2B" 
 myarray(3) = "3A"
 myarray(4) = "4A" 
 myarray(5) = "5C"

Then I want to use the values of the array to perform a CASE search to compile a running total for testing.

My code thus far:

'Create subroutine that will copy and total data from worksheet 1 to worksheet 2
Private Sub VTS()

'Establish variable for CASE to search
Dim ValR As String

'Establish counter array
Dim myarray(1 To 170)

myarray(1) = Worksheets(2).Range("A7").Value
myarray(2) = Worksheets(2).Range("A10").Value

'Dim valves() As String
'Dim thisValue As String

ValR = Worksheets(1).Range("B4").Value

'ValR = Split(valveString, ";")

'valves = ValR

'For v = 1 To UBound(valves)
'    thisValve = valves(v)



Select Case ValR
  Case "1A"
    Worksheets(2).Range("C7").Copy ' Copy current Total
    Worksheets(2).Range("A7").PasteSpecial ' Move to "Previous Total" to sum total
    myarray(1) = Worksheets(1).Range("B3").Value - Worksheets(1).Range("B2").Value
    If myarray(1) < 0 Then
        myarray(1) = 1000000 + myarray(1)
    End If
    Worksheets(2).Range("B7").Value = myarray(1)
    Worksheets(2).Range("C7").Value = Worksheets(2).Range("A7").Value + Worksheets(2).Range("B7").Value
    Worksheets(2).Range("C7").Copy
    Worksheets(1).Range("B10").PasteSpecial
  Case "1B"
    Worksheets(2).Range("C10").Copy
    Worksheets(2).Range("A10").PasteSpecial
    myarray(2) = Worksheets(1).Range("B3").Value - Worksheets(1).Range("B2").Value
    If myarray(2) < 0 Then
        myarray(2) = 1000000 + myarray(2)
    End If
    Worksheets(2).Range("B10").Value = myarray(2)
    Worksheets(2).Range("C10").Value = Worksheets(2).Range("A10").Value + Worksheets(2).Range("B10").Value
    Worksheets(2).Range("C10").Copy
    Worksheets(1).Range("B10").PasteSpecial
  Case Else
    MsgBox "Wrong Model Entered / Model Does Not Exist"
End Select

'Next v

End Sub
Public Sub Call_VTS()
  Call VTS
End Sub

To summarize, I hope to:

1) Build an Array from varying Cell Data

2) Of which, each string represents a different position of the array

3) Then run a CASE search for each position of the array

I've been struggling with this for a couple of days. Any help is appreciated.

score 1 · Accepted Answer · answered Jun 10 '14 at 16:12

To you first question, if Cell B3 contains "1A, 2B, 3A, 4A, 5C" then

myArray = Split(Range("B3"),", ")

will give you the following:

myArray(0) = "1A"
myArray(1) = "2B"
myArray(2) = "3A"
myArray(3) = "4A"
myArray(4) = "5C"

If you want to run through the array and evaluate each item, put a for each loop around your select case:

For Each a In myArray
    Select Case a
      Case "1A"
        Worksheets(2).Range("C7").Copy ' Copy current Total
        Worksheets(2).Range("A7").PasteSpecial ' Move to "Previous Total" to sum total
        myarray(1) = Worksheets(1).Range("B3").Value - Worksheets(1).Range("B2").Value
        If myarray(1) < 0 Then
            myarray(1) = 1000000 + myarray(1)
        End If
        Worksheets(2).Range("B7").Value = myarray(1)
        Worksheets(2).Range("C7").Value = Worksheets(2).Range("A7").Value +     Worksheets(2).Range("B7").Value
        Worksheets(2).Range("C7").Copy
        Worksheets(1).Range("B10").PasteSpecial
      Case "1B"
        Worksheets(2).Range("C10").Copy
        Worksheets(2).Range("A10").PasteSpecial
        myarray(2) = Worksheets(1).Range("B3").Value - Worksheets(1).Range("B2").Value
        If myarray(2) < 0 Then
            myarray(2) = 1000000 + myarray(2)
        End If
        Worksheets(2).Range("B10").Value = myarray(2)
        Worksheets(2).Range("C10").Value = Worksheets(2).Range("A10").Value +        Worksheets(2).Range("B10").Value
        Worksheets(2).Range("C10").Copy
        Worksheets(1).Range("B10").PasteSpecial
      Case Else
        MsgBox "Wrong Model Entered / Model Does Not Exist"
    End Select
Next

If you want to get data from multiple cells and add to the array before looping through, you will need to use a dynamic array and add to it with a ReDim statement. — MatthewHagemann, Jun 10 '14 at 16:14

Use array to search for CASE values Excel VBA

1 Answers1

Linked