0

After a long search, I was not able to find a working answer to my question, so this is my last resort.

I have a For statement looping through an Array. However I want the loop to skip an item at specific points in the Array.

Here is my code so far :

Public Sub test ()

Dim FArray(10, 1) As String
Dim Supported() As Variant

Supported = Array(2, 4, 5)

'Functional Text
FArray(0, 0) = "B30"
FArray(0, 1) = "B3"
'Functional Audio
FArray(1, 0) = "B31"
FArray(1, 1) = "B17"
'PS Text
FArray(2, 0) = "B33"
FArray(2, 1) = "B4"
'PS Audio
FArray(3, 0) = "B34"
FArray(3, 1) = "B18"
'Advanced V2 Text
FArray(4, 0) = "B36"
FArray(4, 1) = "B5"
'Advanced V2 Audio
FArray(5, 0) = "B37"
FArray(5, 1) = "B19"
'Cus Text
FArray(6, 0) = "B39"
FArray(6, 1) = "B6"
'Cus Audio
FArray(7, 0) = "B40"
FArray(7, 1) = "B20"
'Implicit
FArray(8, 0) = "B42"
FArray(8, 1) = "B7"
'OOD
FArray(9, 0) = "B44"
FArray(9, 1) = "B8"
'OOD Audio
FArray(10, 0) = "B45"
FArray(10, 1) = "B21"

For I = LBound(FArray) To UBound(FArray)
    If I = LBound(Supported) To Ubound(Supported) Then

    Else

    idsource = FArray(I, 1)
    OutputCell = FArray(I, 0)
    'Debug.Print (idsource)
    'Debug.Print (OutputCell)

    Call DLResults_generic(idsource, OutputCell)

    End If

Next I

As you can see, I start a For loop using the variable 'I' and then i'm using a second Array (Supported) to mark the points at which I want the for loop to skip. I could do :

If I = 2 Or I = 4 Or I = 5 Then

But I want something that's easier to change, so that I can just go in the Supported Array and put in new values.

I would appreciate it very much if anyone can help me find a way of saying 'If 'I' is in the Supported Array, skip this item in the For loop'

Thanks !

L.P
  • 33
  • 1
  • 2
  • 11
  • Sounds like you need to use a `continue;` in your for-loop, try looking up information about how to use it. – Steven Aug 01 '18 at 13:45
  • 2
    @steven there is no `continue;` in vba. – Vincent G Aug 01 '18 at 13:46
  • Yeah, I've just noticed it by searching myself, I came accross this post, maybe that's a decent replacement: https://stackoverflow.com/questions/5895908/continue-for-loop – Steven Aug 01 '18 at 13:47
  • You can build a user function to check if the value is a part of the array, try to look at https://stackoverflow.com/questions/38267950/check-if-a-value-is-in-an-array-or-not-with-excel-vba/38268261 – Vincent G Aug 01 '18 at 13:50
  • 1
    Untested but perhaps `if isnumeric(application.match(I,Supported,0)) then` – SJR Aug 01 '18 at 13:52

1 Answers1

2

I would make Supported a Dictionary, so that you don't have to iterate it each time to find out whether a value is in it or not:

Set Supported = CreateObject("Scripting.Dictionary") 
Supported.Add 2, True
Supported.Add 3, True
Supported.Add 5, True

And then your loop:

For I = LBound(FArray) To UBound(FArray)
    If Not Supported.Exists(I) Then
        idsource = FArray(I, 1)
        ' ... etc ... 
    End If
Next I
trincot
  • 317,000
  • 35
  • 244
  • 286