The if statement looks at criteria to determine which worksheet to use for the following section. It then sets the sheet name accordingly.

I am unsure what the best way is to put an array in either statement instead of how it currently is with worksheet ranges, since I don't believe I can use the WITH statement to hold the array name that is referenced.

Any help is appreciated. thank you

'sets which date range sheet to use
If spbe30array(i, 22) >= orders Then
    Set spbe = spbe30
Else
    Set spbe = spbe60
End If
            
'the section below is using a sheet name that is determined from the if statement above
i = i   1
With spbe
    Do While LCase(.Range("b" & i).Value) = "placement"
        If LCase(.Range("aa" & i).Value) = "product" Then
            PPclicks = .Range("t" & i).Value
        ElseIf LCase(.Range("aa" & i).Value) = "rest" Then
            ROSclicks = .Range("t" & i).Value
        End If
        i = i   1
    Loop
End With

CodePudding user response：

I think I understand what you want.

You can pull the values from the worksheet after the selection is made using the If statement.

    'sets which date range sheet to use
    If spbe30array(i, 22) >= orders Then
        Set spbe = spbe30
    Else
        Set spbe = spbe60
    End If
       
    Dim b_array() As Variant, aa_array() As Variant, t_array() As Variant
    Dim n_rows As Long
    
    n_rows = 100 'Figure Out how many rows to process
    
    ' Start from row#2 skipping headers
    b_array = spbe.Range("b2").Resize(n_rows, 1).Value
    aa_array = spbe.Range("aa2").Resize(n_rows, 1).Value
    t_array = spbe.Range("t2").Resize(n_rows, 1).Value

             
    'the section below is using a sheet name that is determined from the if statement above
    i = i   1
    Do While LCase(b_array(i, 1)) = "placement"
        If LCase(aa_array(i, 1)) = "product" Then
            PPclicks = t_array(i, 1)
        ElseIf LCase(aa_array(i, 1)) = "rest" Then
            ROSclicks = t_array(i, 1)
        End If
        i = i   1
    Loop

Based on further comments, I recommend using an array of arrays

Dim b_array() as Variant, aa_array() as Variant, t_array() as Variant

b_array = Array( _
    spbe30.Range("b2").Resize(n_rows, 1).Value, 
    spbe60.Range("b2").Resize(n_rows, 1).Value )

aa_array = Array( _
    spbe30.Range("aa2").Resize(n_rows, 1).Value, 
    spbe60.Range("aa2").Resize(n_rows, 1).Value )

t_array = Array( _
    spbe30.Range("t2").Resize(n_rows, 1).Value, 
    spbe60.Range("t2").Resize(n_rows, 1).Value )

and then pick the one to use

    Dim index as Long
    If spbe30array(i,22) >= orders Then
        index = 0
    Else
        index = 1
    End If

    Do While LCase(b_array(index)(i, 1)) = "placement"
        If LCase(aa_array(index)(i, 1)) = "product" Then
            PPclicks = t_array(index)(i, 1)
        ElseIf LCase(aa_array(index)(i, 1)) = "rest" Then
            ROSclicks = t_array(index)(i, 1)
        End If
        i = i   1
    Loop

CodePudding user response：

Jagged Array, ByRef/ByVal

• You could write the values of the ranges to arrays and copy the arrays to another array (jagged array or array of arrays). Then, using the jagged array's index in an IIf function, you could easily decide which array to use.
• To not clutter your code, you could additionally create another procedure to do the job from your With statement.
• The Do Loop still doesn't make sense to me. Maybe you're missing an Exit Do or two.

Option Explicit

Sub Snippet()
    
'    ' Your initial variables (to be able to compile my code)
'    Dim spbe30array As Variant, PPClicks As Variant, ROSclicks As Variant
'    Dim orders As Double
'    Dim i As Long
    
    Dim spbeData As Variant: ReDim spbeData(1 To 2)
    spbeData(1) = spbe30.Value
    spbeData(2) = spbe60.Value
 
    Dim Index As Long: Index = IIf(spbe30array(i, 22) >= orders, 1, 2)
    
    i = i   1
    UpdateClicks spbeData, Index, i, PPClicks, ROSclicks
    ' 'i', 'PPClicks', 'ROSclicks' have been passed 'By Ref',
    ' so they are possibly modified.
    ' If 'i' is not relevant for the continuation, pass it 'ByVal'.
    ' 'spbeData' and 'Index' remain unchanged.
    
End Sub

Sub UpdateClicks( _
        ByVal spbeData As Variant, _
        ByVal Index As Long, _
        ByRef i As Long, _
        ByRef PPClicks As Variant, _
        ByRef ROSclicks As Variant)
        
    Do While LCase(spbeData(Index)(i, 2)) = "placement"
        If LCase(spbeData(Index)(i, 27)) = "product" Then
            PPClicks = spbeData(Index)(i, 20)
        ElseIf LCase(spbeData(Index)(i, 27)) = "rest" Then
            ROSclicks = spbeData(Index)(i, 20)
        End If
        i = i   1
    Loop

End Sub