0

I wrote a program that totals the number of times X number of widgets are tested in a day based upon a start and end count. After a period of time a widget will fail and have to be replaced, thus the count will start back at zero. I am using a Select Case to compute the data and a drop-down menu in Excel to select the widget(s). Everything works great besides one thing... I can't select multiple widgets to search the Case.

I understand the general principles of the Case Statement - but is there any way around searching for only one scenario via a Case? 

'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
...  

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

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

End Sub

Any suggestions?

THANKS!

newtons003
  • 7
  • 6
  • Are you looping through, or selecting each widget and then running the macro one at a time? If the latter, a for next or for each loop sounds like the way to go. If you are already using a for loop, I'm not sure what you are looking for. – MatthewHagemann Jun 09 '14 at 13:31
  • I'm running the macro one at a time. I have an input box that has the start, end and widget(s) used. The user will submit the data then the macro processes. Thanks for the suggestion... I'm not sure how to implement a For Next loop but that gives me something to work on. Thanks again – newtons003 Jun 09 '14 at 13:43
  • It would help if you explained briefly how this sub is getting called from a usage perspective and what "select multiple widget" means (user side and what variable in code and/or Excel cells). – Mark Balhoff Jun 09 '14 at 13:45
  • Sure - Like my comment above - The user is prompted to enter the start count, end count, and which widgets were tested. I'm using a macro that allows the user to select multiple widgets from a drop-down menu and they are separated by a comma. The single search scenario works fine. The user submits the data, the Select Case searches for the correct value and computes the running total. It just doesn't like when I have more than one value to search. – newtons003 Jun 09 '14 at 13:48
  • If I assume ValR represents a widget, then if you wanted multiple you would probably want to change it to an array of widgets. In that case, you could put a for loop around the case statement to loop through the items feeding them to the case statement one at a time. – Mark Balhoff Jun 09 '14 at 13:50
  • Yes, it does. Thanks for the help Mark and Matt! I'll work on it for a bit and see if I have any luck. – newtons003 Jun 09 '14 at 13:53
  • As far as switch case statements go, they do not support being fed multiple values. You have to put a loop around them to create that effect. Sometimes you can also say: 'Select Case var1 & "|" & var2' but I don't think that applies here if my stated assumption is correct. – Mark Balhoff Jun 09 '14 at 13:55
  • Cool - this helps a lot. – newtons003 Jun 09 '14 at 13:56

1 Answers1

0

You can have the user separate widgets by ";" and then use the following loop:

'widgetString = "widget1;widget2;widget3"

widgets = Split(widgetString, ";")

for w = 0 to ubound(widgets)
    thisWidget = widgets(w)

    'place all of your code here, with thisWidget being the current widget being evaluated

next w
MatthewHagemann
  • 1,167
  • 2
  • 9
  • 20
  • I've spent some time on this but keep getting an "Compile Error: Expected Array" with "UBound" highlighted when I have this: Dim valves As String Dim thisValue As String valves = Split(valveString, ";") For v = 1 To UBound(valves) thisValve = valves(v) Select Case thisValve... – newtons003 Jun 10 '14 at 12:10
  • But if I make "valves" into an array --> Dim valves() As String... nothing happens. No error or computation. – newtons003 Jun 10 '14 at 12:15
  • Get rid of your declaration (Dim valves as String) and see if you still get the error. By the way, if you loop v = 1 to ubound, you will miss the first slot, because the array starts at 0, but that's not what's causing your error. – MatthewHagemann Jun 10 '14 at 13:11
  • Hey Matthew - I took that out and now I'm getting a type mismatch. – newtons003 Jun 10 '14 at 15:37
  • On valves? Are you using that variable elsewhere in your code? Split() will produce a string array, so if you use that variable as anything other than a string array, you may get that error. – MatthewHagemann Jun 10 '14 at 15:38
  • I created a new thread because it's hard for me to explain some of this. Take a look: http://stackoverflow.com/questions/24145520/use-array-to-search-for-case-values-excel-vba – newtons003 Jun 10 '14 at 15:55