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

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
270
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,298
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Feb 10, 2016
Messages
270
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

Well-known Member
Joined
May 20, 2004
Messages
3,298
Office Version
  1. 2010
Platform
  1. Windows
ok i think i can still get it to work for you... it is getting late here so i will look tomorrow.

1614855675949.png
 

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
270

ADVERTISEMENT

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

VBA Code:
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

Well-known Member
Joined
May 20, 2004
Messages
3,298
Office Version
  1. 2010
Platform
  1. Windows
vg. i will be available in about 4 hours from now i expect.
 

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
270

ADVERTISEMENT

Great, I look forward to your thoughts.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,298
Office Version
  1. 2010
Platform
  1. Windows
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
 
Last edited:

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
270
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

Well-known Member
Joined
May 20, 2004
Messages
3,298
Office Version
  1. 2010
Platform
  1. Windows
hi. see if i have interpreted your info correctly here :)

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,150
Members
417,010
Latest member
jnuss03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top