Using a Variable array in place of a Vlookup to return values to VBA code

[miless2111s]

I have a set of sheets which contain data which I pull into my VBA code with vlookups. The input data is collected into a table of data 10 columns wide, the key field is always an integer between 1 and 3000 but depending on the data entered by the users I might have 5 entries or 3000. The code loops through all integers 1 to 3000 an inspects the data table, if the integer isn't found it returns a default set of values but if the integer is found the values in the table are used by the code and a range of values are generated.

Is a Vlookup an efficient method? I read somewhere that it might not be and if not should I be using a Variable Array? I have worked out how I can read the input data into an 2D array however I am unsure how I
1) Read down the 1st column of the 2d array to see if the integer value is there (if not return default values)
2) Locate the right "row" in the array and pull out each individual data item into various calculations

I also wonder if outputting an array might be more efficient that my current method which writes out each result cell by cell (the output from the entire code is 2,500 rows of 31 columns of data which couldn't be output in one array but maybe 2,500 array writes would be faster than 77K individual write operations?

Many thanks in advance

Miles

 

[diddi]

if your "key field" always starts at 1 and contains all numbers to your upper limit, then managing your data as a VBA array will be far quicker that using lookups on sheets. so is it the case at the moment that when you write the record of data for a key field, you write the key number and then the data? ie, the records unused do not contain any data (or a key)
if that is the idea of how it works then i can assist when you are available to answer questions so we can get a solution to your problem sorted quickly

 

[miless2111s]

Diddi
My source data will not always start at 1 and will certainly not always contain all the integer values from 1-3000.
Say for instance my source data has 10 rows which have the key field values of 2,3,4,5,8,9,10,150,250,251. All these entries will have their 10 values populated.
The code will loop through all key values from 1-300 with the steps:

For Key_Value = 1 to 3000
If key value is not present
- Define 31 default settings which will be written to the output table
Else
- Vlookup the first of the 10 values, do something with it, define a result
- Vlookup the 2nd of the 10 values, do something with it, define a result
- .....
End if
- Write the defined values to the 31 cells on the output table one cell at a time
- Key_Value = Key_Value +1
Next Key_Value

I am writing in fake code as the actual code is too long to be usefully transposed here. Please let me know if there is anything else I need to expand on to help

Regards

Miles
PS how do I get the indent to "stick"? I see it when I'm typing my reply but when it displays it doesn't show I've replaced spaces with dashes to try to make it easier to read.

 

[diddi]

ok i think i can still get it to work for you... it is getting late here so i will look tomorrow.

 

[miless2111s]

Thank you Diddi. I'll try the code snippet tool as a test

For Key_Value = 1 to 3000
If key value is not present
  Define 31 default settings which will be written to the output table
Else
  Vlookup the first of the 10 values, do something with it, define a result
  Vlookup the 2nd of the 10 values, do something with it, define a result
  .....
End if
Write the defined values to the 31 cells on the output table one cell at a time
Key_Value = Key_Value +1
Next Key_Value

 

[diddi]

vg. i will be available in about 4 hours from now i expect.

 

[miless2111s]

Great, I look forward to your thoughts.

 

[diddi]

if you are about we can have a look now
first up. where are the Key values stored?
and can a key value be repeated in the output table
where is the default table stored

 

[miless2111s]

Diddi
The input table which contains the Key Values can only have each key value in once (as it is fed by a pivot table). There is no certainty about which key values will appear in the table
The output table will only have the key value in once and will contain every key value
The "default table" doesn't exist - the values are assigned in the code. The reason is that key values 1-N will have one set of default values and N=1 - X will have another set.

 

[diddi]

hi. see if i have interpreted your info correctly here

Option Base 1
Dim AllData(3000, 10)
' The input data is in a sheet called "InputData"
' The data is collected from A1 to J(n)

' The output data after processing is on "OutputData"
' The Default data sets may be set using the two constant arrays, or i can insert your code to suit

Sub LoadData()
    Dim MyIdx As Long, Row As Long, Col As Long
   
    DefaultVals1 = Array("val1", "val2", "val3", "val4", "val5", "val6", "val7", "val8", "val9", "val10")
    DefaultVals2 = Array("Dat1", "Dat2", "Dat3", "Dat4", "Dat5", "Dat6", "Dat7", "Dat8", "Dat9", "Dat10")
   
    With Sheets("InputData")
        For Row = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
            MyIdx = .Cells(Row, 1)
            If MyIdx <= 3000 Then
                For Col = 1 To 10
                    AllData(MyIdx, Col) = .Cells(Row, Col)
                Next Col
            End If
        Next Row
    End With
    For Row = 1 To 3000
        If AllData(Row, 1) < 1 Then
            If True Then 'criteria for default 1
                For Col = 1 To 10
                    AllData(Row, Col) = DefaultVals1(Col)
                Next Col
            Else
                For Col = 1 To 10
                    AllData(Row, Col) = DefaultVals2(Col)
                Next Col
            End If
        End If
    Next Row
   
    Sheets("OutputData").Range("A1:J3000") = AllData
End Sub