Using a variable inside code

[Lavina]

Hello guys,

I'm trying to minimize lines that i have in my code and i ran into a small problem I'm not sure how to approach

I have 3 arrays that i fill up depending on a variable:

If fullArray(i, 2) >= 1 And fullArray(i, 2) < 3 Then
arrayInQuestion = "sheet2"
If fullArray(i, 2) >= 3 And fullArray(i, 2) < 6 Then
arrayInQuestion = "sheet3"
If fullArray(i, 2) >= 6 And fullArray(i, 2) <= 10 Then
arrayInQuestion = "sheet4"

Then i have small cycle to work transfer data to array:

If fullArray(i, 2) >= 3 And fullArray(i, 2) < 6 Then
If (Not sheet3) = -1 Then
positionX = 1
Else: positionX = UBound(sheet3, 2) + 1
End If
ReDim Preserve sheet3(1 To 2, 1 To positionX)
For k = 1 To lastColumn - 1
sheet3(k, UBound(sheet3, 2)) = fullArray(i, k)
Next k
End If

Currently i have 3 ifs that all run the same procedure, except target different arrays. Is there a way i can use the initial arrayInQuestion setter to acquire a variable i can use in my code?

Something like:

If (Not arrayInQuestion) = -1 Then
positionX = 1
Else: positionX = UBound(arrayInQuestion, 2) + 1
End If
ReDim Preserve arrayInQuestion(1 To 2, 1 To positionX)
For k = 1 To lastColumn - 1
sheet2(k, UBound(sheet2, 2)) = fullArray(i, k)
Next k
fullArray(i, 3) = "Sheet2"
End If

 

[Lavina]

The solution was to use a function:

If fullArray(i, 2) >= 1 And fullArray(i, 2) < 3 Then sheet2() = fillUpArray(sheet2())
If fullArray(i, 2) >= 3 And fullArray(i, 2) < 6 Then sheet3() = fillUpArray(sheet3())
If fullArray(i, 2) >= 6 And fullArray(i, 2) <= 10 Then sheet4() = fillUpArray(sheet4())

Function fillUpArray(arrayInQuestion() As Variant)
If (Not arrayInQuestion) = -1 Then
positionX = 1
Else: positionX = UBound(arrayInQuestion, 2) + 1
End If
ReDim Preserve arrayInQuestion(1 To 2, 1 To positionX)
For k = 1 To lastColumn - 1
arrayInQuestion(k, UBound(arrayInQuestion, 2)) = fullArray(i, k)
Next k
fillUpArray = arrayInQuestion()
End Function