Using Select Case with 1D arrays

[Coldfire46]

Hi All,

I'm trying to use select case to take some data from a 1D array and place them in specific columns in a new worksheet; I've attempted to write the code and having never used select case before I know I've not done it quite right, but hopefully it'll help to explain what I'm trying to do...the code works fine up to the line that starts with Location = ws.range(etc...)

Any help would be much appreciated.

Private Sub CommandButton1_Click()
Dim wbk As Workbook
Dim ws As Worksheet
Dim WS_string As String
Dim EndRow As Long
Dim Labour_InfoAr() As Variant
Dim Counter As Long, i As Long
Dim v As Integer
Dim x As Long
Dim y As Long
Dim Location As Range
Dim ColOffset As Long
Dim Desc As Integer
Dim WBS As Integer
Dim Resource As Integer
Dim Total As Integer
Dim Bidder As Integer
Dim a As Integer
Dim TempAr() As Variant

Desc = 1
WBS = 2
Resource = 3
Total = 4
Bidder = 5
Application.ScreenUpdating = False
Set wbk = Workbooks("PrimeSpreadsheet(v4).xlsm")
ReDim Labour_InfoAr(1 To 5, 1 To 1)
Counter = 0
ColOffset = 8
'for each ws
For Each ws In wbk.Worksheets
    WS_string = ws.Name
    'if this is the summary sheet
    If Left(WS_string, 7) = "SUMMARY" Or Left(WS_string, 7) = "Summary" Or Left(WS_string, 7) = "summary" Then
        'look through the required range for values
        EndRow = ws.Range("A:A").Find(what:="*", searchdirection:=xlPrevious, after:=[A1]).Row '& Rows.Count
        For y = 15 To EndRow
            For x = 0 To ColOffset
                'if values then add to array with supporting info
                If ws.Cells(y, 7).Offset(0, x).Value <> 0 Then
                    Counter = Counter + 1
                    ReDim Preserve Labour_InfoAr(1 To 5, 1 To Counter)
                    Labour_InfoAr(Desc, Counter) = ws.Cells(y, 3).Value   ' Desc
                    Labour_InfoAr(WBS, Counter) = ws.Cells(y, 6).Value     'WBS
                    Labour_InfoAr(Resource, Counter) = ws.Cells(13, 7).Offset(0, x).Value  'Resource
                    Labour_InfoAr(Total, Counter) = ws.Cells(y, 7).Offset(0, x).Value   'Total
                    Labour_InfoAr(Bidder, Counter) = ws.Cells(EndRow, 2).Offset(4, 0).Value 'Bidder
                End If
            Next x
        Next y
    End If
Next ws
Set ws = Sheets("Export to Auto-Loader")
EndRow = ws.Range("A:A").Find(what:="*", searchdirection:=xlPrevious, after:=[A1]).Row
For v = 1 To 5
    ReDim TempAr(1 To Counter)
    For i = 1 To Counter
        TempAr(i) = Labour_InfoAr(v, i)
    Next i
    'Location where data in array will sit within the Export to Auto-Loader worksheet
    
    Location = ws.Range(ws.Cells(), ws.Cells())
        Select Case Location
        Case i = 1 To 5
        
            Case i = 1
                ws.Range(ws.Cells(2, 2), ws.Cells(Counter, 2)) = TempAr
            Case i = 2
                ws.Range(ws.Cells(2, 6), ws.Cells(Counter, 6)) = TempAr
            Case i = 3
                ws.Range(ws.Cells(2, 15), ws.Cells(Counter, 15)) = TempAr
            Case i = 4
                ws.Range(ws.Cells(2, 17), ws.Cells(Counter, 17)) = TempAr
            Case i = 5
                ws.Range(ws.Cells(2, 8), ws.Cells(Counter, 8)) = TempAr
        End Select
Next v
Application.ScreenUpdating = True
End Sub

 

[Comfy]

Your Range Object "Location" should be set using either of the folllowing:

Set Location = ws.Range("A1:B5")

'or

Set Location = ws.Range(Cells(1,1), Cells(5,2))

Secondly your Select Case is wrong.

You are wanting to evaluate the values of Location (which is a range object) but are then using the object i (which is Long)

I assume that this Select Case should be in the previous loop of i???

So, ignoring Location for the moment.

For i = 1 To Counter
        TempAr(i) = Labour_InfoAr(v, i)

Select Case i        
            Case i = 1
                ws.Range(ws.Cells(2, 2), ws.Cells(Counter, 2)) = TempAr(i)
            Case i = 2
                ws.Range(ws.Cells(2, 6), ws.Cells(Counter, 6)) = TempAr(i)
            Case i = 3
                ws.Range(ws.Cells(2, 15), ws.Cells(Counter, 15)) = TempAr(i)
            Case i = 4
                ws.Range(ws.Cells(2, 17), ws.Cells(Counter, 17)) = TempAr(i)
            Case i = 5
                ws.Range(ws.Cells(2, 8), ws.Cells(Counter, 8)) = TempAr(i)
        End Select

   Next i

or as you have specifically set the numbers as 1 to 5 should it be referring to your loop of v which is also 1 to 5??

Without seeing the data you are working with and without an understanding of what you are trying to achieve this is purely speculation.