Excel question using complex arrays and conditional statements

[JLoewen]

I am not sure whether to do this with native Excel or with VBA.

I have a complex spreadsheet (for me anyway) that is receiving input from other spreadsheets. The other spreadsheets are the "Site Sheets" listed in the log below. On each of these sheets are records I need to essentially combine into the Dashboard sheet. The other total spreadsheet list needs to be somewhat dynamic. Right now, I have it setup so that the user (who is NOT an Excel guy) can easily import new site sheets just by duplicating the old sheets, copying in the file path and clicking a button to update links on that sheet.

I also have the Dashboard sheet setup to index and match through addresses that I need to generate here. I've manually generated the addresses, used INDIRECT to make the sheet addresses variable based on the sheet name, and tested that functionality.

The functionality I need to figure out is how to generate my addresses for each record.

The addresses will look like this:

That is, from the stuff in RED above, I need to generate an array with the record number, from 1 to 246 (variable, driven) with a list of sites (Ft. Myers) and 1-75 job index numbers for Ft. Myers (variable number, driven by the value in the red cells) followed by another series of records (76-125) for (Hammond) with 1-49 job index numbers, etc.

I attempted doing this with VBA but ran into issues with the syntax for arrays. I am not sure what I am doing wrong. I am also unsure if it wouldn't make more sense to use a native Excel function but I'm also not sure how to do that. Variable length and flexible arrays are a bit beyond me. I tried doing a 2-D array and gave it up due to ignorance. Here's a snap-shot of an attempt with a 1-D array and an error that pops up. I am not sure what my syntax error is. If you have any advice, please help.

 

[JLoewen]

I have been chewing on this and I think I have the logic figured out but don't have the syntax figured out. I think VBA is the way to go.

Static Function arrayplay()
Dim inputarray, outputarray As Variant
Dim n, m, l, k, j, i, totalrecords, NumsheetS As Integer

totalrecords = Worksheets("peripheral").Range("E3").Value
NumsheetS = Worksheets("Peripheral").Range("C2").Value

inputarray = Worksheets("Peripheral").Range("A5:D500").Value

For n = 1 To n = totalrecords
outputarray(n, 1) = n
For i = 1 To i = NumsheetS
If inputarray(3, i) = "T" Then
For k = 1 To k = inputarray(4, i)
outputarray(3, n) = k
outputarray(2, n) = inputarray(2, i)
Next k
End If
Next i

Next n

Worksheets("Peripheral").Range("F5:H5").Value = outputarray


'Worksheets("Peripheral").Range("K10").Value = inputarray(4, i)

End Function

Running into issues with a syntax error. It skips over the first FOR loop. Any thoughts?

 

[JLoewen]

Here's a cleaner look at the code. It's not skipping over everything inside the first FOR loop.

Function arrayplay()
Dim inputarray(), outputarray() As Variant
Dim n, m, l, k, j, i, totalrecords, NumsheetS As Integer

totalrecords = Worksheets("peripheral").Range("E3").Value
NumsheetS = Worksheets("Peripheral").Range("C2").Value

inputarray = Worksheets("Peripheral").Range("A5:D500").Value

ReDim outputarray(totalrecords, 3)

For n = 1 To n = totalrecords
    outputarray(n, 1) = n
    For i = 1 To i = NumsheetS
       If inputarray(3, i) = "T" Then
            For k = 1 To k = inputarray(i, 4)
                outputarray(n, 3) = k
                outputarray(n, 2) = inputarray(i, 2)
            Next k
        End If
        Next i
            
    Next n


Worksheets("Peripheral").Range("F5:H5").Value = outputarray(1, 1)


'Worksheets("Peripheral").Range("K10").Value = inputarray(4, i)

End Function

 

[offthelip]

I am guessing a bit about what you are trying to do, there were lots of errors in your code. I have tried to correct them, this code will compile and run, whether ti does what you want is another matter:

'Static Function arrayplay()
Sub arrayplay()
Dim inputarray
Dim outputarray() As Variant
Dim n, m, l, k, j, i, totalrecords, NumsheetS As Integer ' this statement defines Numsheets as integer but all the rest  as variant.

totalrecords = Worksheets("Peripheral").Range("E3").Value
NumsheetS = Worksheets("Peripheral").Range("C2").Value

inputarray = Worksheets("Peripheral").Range("A5:D500").Value
ReDim outputarray(1 To totalrecords, 1 To 3)
outi = 1
For n = 1 To totalrecords
    For i = 1 To NumsheetS
    If inputarray(i, 3) = "T" Then
        tt = inputarray(i, 4)
        If tt = "" Or tt = 0 Then tt = 1
        For k = 1 To tt
        outputarray(outi, 1) = n
        outputarray(outi, 3) = k
        outputarray(outi, 2) = inputarray(i, 2)
        outi = outi + 1
        Next k
    End If
    Next i
Next n

Worksheets("Peripheral").Range(Cells(5, 6), Cells(5 + totalrecords, 8)).Value = outputarray


'Worksheets("Peripheral").Range("K10").Value = inputarray(4, i)

End Sub

 

[JLoewen]

Thank you. I can see the errors I made in the syntax. I got this working now and would have spent hours getting frustrated with the FOR loop without your help.

 

[JLoewen]

I do have another question. I am getting an odd result at the end of the computation run.

I am not sure why it's evaluating a cell as #N/A at the end. The total record count is 185. I almost wonder if there's not something in the write function that's adding one row at the end of the array, a row that is undefined because outputarray only goes to long.

Function recordsindex()
Dim inputarray(), outputarray() As Variant
Dim n, m, l, k, j, i, totalrecords, Numsheets, recordcount As Integer

totalrecords = Worksheets("peripheral").Range("E3").Value
Numsheets = Worksheets("Peripheral").Range("C2").Value

inputarray = Worksheets("Peripheral").Range("A5:D500").Value

ReDim outputarray(1 To totalrecords, 1 To 3)

'initialize spreadsheet values to zero
Worksheets("peripheral").Range("F5:H10000").Value = ""

'initialize values in outputarray to zero
For i = 1 To totalrecords
    outputarray(i, 1) = ""
    outputarray(i, 2) = ""
    outputarray(i, 3) = ""
    Next i

recordcount = 1

For n = 1 To Numsheets
    If inputarray(n, 3) = "T" Then
        For i = 1 To inputarray(n, 4)
            outputarray(recordcount, 1) = recordcount
            outputarray(recordcount, 2) = inputarray(n, 2)
            outputarray(recordcount, 3) = i
            recordcount = recordcount + 1
            Next i
        End If
              
    Next n

Worksheets("Peripheral").Range(Cells(5, 6), Cells(5 + totalrecords, 8)).Value = outputarray


End Function

Addtional question, why define a function as SUB, FUNCTION, etc.? How does that work?

Regards,

 

[offthelip]

The #N/A at the bottom of the output is my mistake, the size of the output range is 1 row larger than the size of the array. so nothing is written into it. to fix this either increase the size of the array by one:

ReDim outputarray(1 To totalrecords+1, 1 To 3)

and then change the initialisation by 1 as well.
OR
reduce the size of the output range by 1:

Worksheets("Peripheral").Range(Cells(5, 6), Cells(4 + totalrecords, 8)).Value = outputarray

The different between a function and subroutine is a subroutine can only be called from VBA, while a function can be used on the worksheet. These are Known as User defined functions.
Your code is not suitable to be a function. A function must return a value which will appear in the cell if you use it on the worksheet

 

[JLoewen]

What type of operation would it be if I want it to be called by a radio button click that is located on a sheet besides the one where the input/output are coming from/to?

 

[offthelip]

A subroutine