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.

 

[MARK858]

In account go to About Excel and at the bottom it should state what channel you are on (I would guess by your build that it is Bi-Annual)...

 

[ShaunH]

In account go to About Excel and at the bottom it should state what channel you are on (I would guess by your build that it is Bi-Annual)...

View attachment 5586

Hi yes, you are correct. Unfortunately I have no control over that, unless I change it in the registry. Will just have to be patient.

 

[MARK858]

Hi yes, you are correct. Unfortunately I have no control over that, unless I change it in the registry. Will just have to be patient.

Not as such a registry setting, it is an initial Account set up (I guess businesses pay less for the Bi-Annual set-up but haven't looked it up to be sure).

 

[MARK858]

Been doing a read up and the semi-annual setup is the default setting for proplus but your IT dept should be able to change you to the monthly channel if you can convince them... Can't see anything that says they have to pay more.

 

[ShaunH]

Thanks for the help, I was able to change settings in registry to enable to current version. Was set to group policy. I suppose they need us to use the same versions throughout.

 

[MARK858]

I suppose they need us to use the same versions throughout.

Microsoft don't but your IT dept. might

 

[ShaunH]

Microsoft don't but your IT dept. might

It would make everyone's spreadsheets compatible when opening on any machine. I think we may be due for an update, as the last update was Feb 2019, excluding security patches.
My registry changes were picked up by group policies and they changed it back. However it did start downloading the latest November update. Then failed when the install was supposed to start. Office is rediculously expensive for personal use if buying 2019 Pro. But thanks for the info.

 

[MARK858]

Office is rediculously expensive for personal use if buying 2019 Pro

Why would you need to buy Pro for personal use? besides 2019 doesn't have the array functions. They are in 365.

 

[ShaunH]

Why would you need to buy Pro for personal use? besides 2019 doesn't have the array functions. They are in 365.

Thank you for the info. Will probably do a 365 Home then. My son will need to start learning this stuff too sometime.