Efficient way to use index match or alternative method to insert values dynamically into worksheet

[ShaunH]

Hi everyone. I am stuck.
I need to fill values into columns using VBA in much the same way as INDEX and MATCH does, but using VBA. I have everything turned off already.

My data uses VBA to copy select columns from one sheet to a new sheet "Analyse" in Columns A to G. That works perfectly. The rows are dynamic as the source data changes occasionally.

Here is the problem: I need to lookup from a table on one sheet "GroupData" Column 1 and return column 2 and 3 in H and I,

using entries in column D as my MATCH.​

Then there is another lookup in a another sheet and table "SCPvalue", that looks at column 1 and returns column 2,

using entries in "Analyse" column E as my MATCH.​

All columns have headers as they are tables

It works fine if the formula is in the cell, but that is easily broken and has to remain even if the sheet has no values as that is something that does happen, which has it's own problems.
I have other code that also works by using VBA, but it is slow and still incomplete.

I clear the sheet from row 2 down before filling the new data. Data can be anything from 1 to 2000 rows of data.
The lookup is applied after the data is filled. "GroupData" is a Customer, Holding Company and Group list of 7000 rows, which can grow beyond 7000.
The other lookup table is only about 10 rows and two columns, dimSCP and SCPvalue, unlikely to change, but if it does, I need to make provision for it.

This is the slow incomplete VBA. It takes about 10 seconds to run through the customer database. The othe part is incomplete.

Sub FillValues()
Dim strThisFile As String
strThisFile = "TestFile.xlsm"

Dim strSheetA As String
strSheetA = "Analyse"
Dim strSheetG As String
strSheetG = "GroupData"
Dim strSheetS As String
strSheetS = "SCPvalues"

Dim intCounterA_Y As Integer
intCounterA_Y = 2
Dim intCounterG_Y As Integer
intCounterG_Y = 2
Dim intCounterS_Y As Integer
intCounterS_Y = 2

While Workbooks(strThisFile).Sheets(strSheetA).Cells(intCounterA_Y, 1).Value <> "" ' Loop through Anlayse
intCounterG_Y = 2
While Workbooks(strThisFile).Sheets(strSheetG).Cells(intCounterG_Y, 1).Value <> "" ' Loop through GroupData
If Workbooks(strThisFile).Sheets(strSheetA).Cells(intCounterA_Y, 4).Value = Workbooks(strThisFile).Sheets(strSheetG).Cells(intCounterG_Y, 1).Value Then
Workbooks(strThisFile).Sheets(strSheetA).Cells(intCounterA_Y, 8).Value = Workbooks(strThisFile).Sheets(strSheetG).Cells(intCounterG_Y, 3).Value
Workbooks(strThisFile).Sheets(strSheetA).Cells(intCounterA_Y, 9).Value = Workbooks(strThisFile).Sheets(strSheetG).Cells(intCounterG_Y, 2).Value
End If
intCounterG_Y = intCounterG_Y + 1
Wend




intCounterA_Y = intCounterA_Y + 1
    Wend

End Sub

The code only runs once after the data has been copied to "Analyse" sheet. It does not need to watch for changes to this sheet.
I have seen some rather shortened versions of VBA, that claim to be much faster, I cannot say. I'm still learning though.

Please help to either rewrite in a more efficient way, or point me in the right direction?
Also I would appreciate an explanation of the steps to help me learn if possible.

Thank you in advance.

 

[FatBoyClam]

You say the formulas work well if input into the cells. Have you considered just re-writing the formulas to the cells using VBA?

Sub FillValues()

Dim shtAnalyse As Worksheet, wb As Workbook, iGDRow As Long, iSCPRow As Long, iARow As Long

Set wb = Workbooks("TestFile.xlsm")

Set shtAnalyse = wb.Worksheets("Analyse")

iGDRow = wb.Worksheets("GroupData").Range("A2").End(xlDown).Row
iSCPRow = wb.Worksheets("SCPValue").Range("A2").End(xlDown).Row
iARow = shtAnalyse.Range("A2").End(xlDown).Row

shtAnalyse.Range("H2").Formula = "=INDEX(GroupData!B$2:B$" & iGDRow & ", MATCH($D2, GroupData!$A$2:$A$" & iGDRow & ",0))"
shtAnalyse.Range("I2").FormulaR1C1 = shtAnalyse.Range("H2").FormulaR1C1
shtAnalyse.Range("J2").Formula = "=INDEX(SCPValue!B$2:B$" & iSCPRow & ", MATCH($E2, SCPValue!$A$2:$A$" & iSCPRow & ",0))"

shtAnalyse.Range("H2:J2").Copy

shtAnalyse.Range("H3:J" & iARow).PasteSpecial xlPasteFormulas

Application.CutCopyMode = False

End Sub

That finds the last row on each of the sheets with data (assumption here is that there is at least 2 rows of data on each sheet), then writes the index/match formula to the columns on row 2 of the Analyse sheet (assumption is that the value fetched from SCPValues is to go in column J), then copies those formulas down as far as the data in the Analyse sheet goes down.

Does that help?

Alternatively, you could do the lookup in VBA, as you have done, but write the results to an array (an area of memory where you can store a set of values), then write the array back as one lump to the Analyse worksheet. This is more efficient because the 'write' action of excel putting a value in a cell takes about as long if you write 1000 values at once as it does for just one value. In your code you're writing to 2 (eventually 3) cells individually for each row on Analyse (about 2000 rows = 6000 cells). You could create an array that size (2000 rows by 3 columns), fill it with the values in your loops

Separately - as an observation on coding - I've created an object variable to represent the Analyse worksheet (shtAnalyse), which I've declared to be a sheet in the Workbook object variable "wb" which represents the workbook "TestFile.xlsm". This makes the code a bit neater than explicitly referring to the items in the workbook and worksheets collections as you have done. That said - most people don't take the time to explicitly qualify the workbook and sheet of the cells they're referring to, so +1 for deliberately specific coding ??

 

[ShaunH]

Thanks for the quick reply, I will try this above, although I have tried inserting the formula's by VBA before, but that didn't work as it treated the formula as text.
I then looked at other alternatives. I am not very good at VBA I know some but most likely enough to be dangerous.

Can I post a sample workbook? It has very few lines of data in it, however the actual one, has currently almost 7000 rows in the lookup table, the other table only about 10. The "lookups" range from anything from 1 row of data to a maximum of 2000 as the source can only display 2000 rows.

I like the inarr method that was posted in Vlookup VBA Alternative by offthelip, It is beyond me, but I think arrays the better way than inserting the formula.
I would love it if you could look at my whole module. The way it all works, I have to have compatibility for Win 7 and Office x32 which is in my module, then I've broken up the code into smaller parts to perform it's function. Which copies columns from a hidden sheet, I had an error handler, but that doesn't work anymore.
After that the above code is applied to fill the values to the sheet.
Would it be possible? It's not a large file, and if so, how do I go about it or should I post the whole module's code on here.
I'm comfortable with the other things that are in the worksheet, although also pretty slow.

For that I'll post a separate question on the forum.
I'll try the above, although I may wait for your reply. Thank you. Really appreciated.

 

[Akuini]

Can I post a sample workbook?

We can't attach a file in this forum, but you can upload your sample workbook to a free site such as dropbox.com or goole drive & then put the link here.

 

[ShaunH]

I tried the code above on 19 rows of data, but it fills the formula all the way down to Row 1048576. Also I had my columns wrong way round in GroupData, Columns B should be in I and C in H. Not a major problem. But since it's filling all the way to the last Excel row, it is very slow and not what it should be doing. I also get three popups after the code has run that have a heading "Update Values: SCPValue" which looks like a file selection window. This is in my production workbook copy.

I tried a similar method before as you have shown but did not work as expected.

I will update the code in my sample workbook and post a link, I may have to create an account first. I've commented out the methods I've tried, or left their code, but not using them as I call them from another macro.

Thank you very much.

 

[FatBoyClam]

It should fill down as far as there is data in Column A on the Analyse sheet (assuming there is more than one row of data).

Your code used Column A of the Analyse sheet to loop through, stopping at an empty cell, so I figured that should work for you.

There's actually another way you can tackle this that uses inbuilt functionality.

If you make the range of cells on Analyse sheet a table - insert some data, select a cell in it, and on the Home ribbon, select Styles>Format as Table. Excel should automatically pick the range with data in, plus the three columns where the formulas will go, because you've given them headers. You'll be asked for a style - pick any one. A dialogue box will show asking where the data is (it should autopopulate with the range of data plus 3 columns). Make sure "My table has headers" is selected, and click OK.

The data will now be defined as a table, and when you have a cell in the table selected the Ribbon will how a "Table Tools>Design" tab (step 1: rename the table by overtyping the value in the Table Name box).

If you enter the formulas in the top row of the blank columns, Excel will autofill the formulas down the columns. The really handy bit here is that, when you delete the data, ready to re-populate with the next extract Excel will remember what the formulas were and refill the columns when you reinsert the data.

It's very straightforward to work with the table in VBA. Let's assume you named the table "tbl_Analyse", and your data comes from another workbook called "DataSource" with a tab called "Data", populated from cell A1, with headers.

Sub RefillTable()

Dim wbData As Workbook, shtData As Worksheet, rngSource As Range, shtAnalyse As Worksheet, tblAnalyse As ListObject

Set wbData = Workbooks("DataSource")
Set shtData = wbData.Worksheets("Data")

'Set the range to pick up all data in a contiguous range from A1
Set rngSource = shtData.Range("A1").CurrentRegion

'remove the top row (headers) of the data source range
Set rngSource = rngSource.Offset(1, 0).Resize(rngSource.Rows.Count - 1, rngSource.Columns.Count)

Set shtAnalyse = ThisWorkbook.Worksheets("Analyse")

Set tblAnalyse = shtAnalyse.ListObjects("tbl_Analyse")

'If the table has data, delete it.
If Not tblAnalyse.DataBodyRange Is Nothing Then
    tblAnalyse.DataBodyRange.Delete
End If

tblAnalyse.InsertRowRange.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value


End Sub

So - having done the ground work of setting up the table for your imported data with the formulas, that will populate it, and extend the formulas down to the last row of data.

Now we've introduced the idea of tables, I'd go back to the start, and put your two lookup ranges into tables as well, before you write the formulas. Then the formulas can use the tables as references, rather than explicit cell ranges, which means the formulas will always work, no matter how many rows your lookup tables contain....

To Summarise:

Step 1, format your GroupData lookup range as a table, name it "tbl_GroupData"
Step 2, format your SCPValues lookup range as a table, name it "tbl_SCPValues"
Step 3, drop a sample set of data into your Analyse tab, make sure the adjacent 3 formula columns have headers, but are otherwise blank.
Step 4, format that range as a table, name it "tbl_Analyse"
Step 5, enter the following formulas in row 2, Columns H, I and J (where the formula has "ColumnA", "ColumnB", "ColumnC" etc, replace that value with the header from the lookup table but keep the square brackets. The ones with @ symbols are referencing the Analyse table, so don't need the tablename. The @ symbol means "the value on this row from the named column", you'll find that excel will help with Intellisense prompts as you type...);

=INDEX(tbl_DataGroup[ColumnC], match([@ColumnD], tbl_DataGroup[ColumnA], 0)
=INDEX(tbl_DataGroup[ColumnB], match([@ColumnD], tbl_DataGroup[ColumnA], 0)
=INDEX(tbl_SCPValues[ColumnB], match([@ColumnE], tbl_SCPValues[ColumnA], 0)

After entering each formula, Excel will populate the rest of the column.

Step 6, use the code above (adapted to suit where your data comes from) to import the data to the Analyse sheet.

 

[ShaunH]

I have tried to install dropbox, not able to install. All the sheets are tables. I use a query to update my Customer data, as well as the Group data. They are then actually loaded to the Workbook. Then the Analyse sheet is a table too, called "AnalyseTable". I have been busy getting the Sample Workbook together. Was just about to put the file on Google Drive. Who do I share it with?

 

[FatBoyClam]

I think you'd need to make a public URL ("get sharable link" - anyone with the URL can access it).

Make sure the data does not contain anything personally or commercially sensitive (make up fake, but representative data if needs be).

You say the Customer and Group data are from Queries - do you have the connections built into the workbook? If so, be wary of that containing sensitive info, such as database usernames or passwords.

 

[ShaunH]

The link to the Sample Workbook. Sample5.xlsm

 

[ShaunH]

The sample is completely manually created. Not connected to any queries that I can find.