Excel VBA Lookup syntax error fix or alternative

[renigar]

I have a master sheet "Penalties" that I need to update with monthly data. I need to do a lookup without writing formulas on the sheet. The data sheet "November1" has only a portion of the data that applies to the Month of November. More data will come in weekly.

The "November1" (data) sheet has 2,459 rows (this number will vary). Row 1 is a header, data starts at row 2. Column K has the data (number) to retrieve. Column J has the lookup key (number).

The Penalties (master) sheet has 25,923 rows (may vary some). There are headers and data starts at row 7. Column BW has the lookup key (number).

In this case if I use Vlookup or Index Match I would have to loop through the master sheet which I think will take along time due to the size of the sheet. I was trying to use Index Match to see just how long but I am doing something wrong. I haven't found a good example through Google. I tried a routine with Autofilter and looping through the data sheet, way to slow at 3 hrs 20 min in this case. Then I tried another routine with Find looping through the data sheet, much better at 22.5 min. Is there a better way? If not I'll stick with my Find routine. Thanks.
renigar

With this code I get "Unable to get the Match property of the WorksheetFunction class", Run-time error 1004.

Option Explicit
Sub TestLookup()
Dim ConsValue As Long
Dim i As Long
Dim dLastRow As Long
Dim pLastRow As Long

' Get the number of the last row of the data import sheet
    dLastRow = Sheets("November1").Cells(Rows.Count, 1).End(xlUp).Row
    
' Get the number of the last row of the Penalties sheet
    pLastRow = Sheets("Penalties").Cells(Rows.Count, 1).End(xlUp).Row

' Loop through the data import sheet to get consumption values
For i = 2 To pLastRow

'Lookup the consumption value on the November1 sheet

    ConsValue = WorksheetFunction.Index(Sheets("November1").Range("K2:K" & dLastRow), _
        WorksheetFunction.Match(Sheets("Penalties").Cells(i, 75), Sheets("November1").Range("J2:J" & dLastRow), 0))
    
    MsgBox "Consumption = " & ConsValue & " Units"

' Continue loop
Next i

End Sub

 

[SkipVought]

Hi,

On this particular issue...

For i = 2 To pLastRow

'[COLOR=#D3D7CF]Lookup the consumption value on the November1 sheet

    ConsValue = WorksheetFunction.Index(Sheets("November1").Range("K2:K" & dLastRow), _
        WorksheetFunction.Match(Sheets("Penalties").Cells(i, 75), Sheets("November1").Range("J2:J" & dLastRow), 0))[/color]
    
    [b]Debug.Print[/b] "Consumption = " & ConsValue & " Units"

' Continue loop
Next i

…I'd use Debug.Print. Things will go lots faster. Right now you're just checking to determine if your lookup is working.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[mintjulep]

Read the cell ranges into arrays and work with arrays in VBA.

Much faster.

 

[SkipVought]

If I were going to clean this up and make it run faster...

Sub TestLookup()
Dim ConsValue As Long
Dim i As Long
Dim dLastRow As Long
Dim pLastRow As Long
[b]Dim rNovK as Range
Dim rNovJ as Range
Dim rPen as Range
Dim xl as Application[/b]

' Get the number of the last row of the data import sheet
    dLastRow = Sheets("November1").Cells(Rows.Count, 1).End(xlUp).Row
    
' Get the number of the last row of the Penalties sheet
    pLastRow = Sheets("Penalties").Cells(Rows.Count, 1).End(xlUp).Row

' [b]set fixed ranges
Set rNovK = Sheets("November1").Range("K2:K" & dLastRow)
Set rNovJ = Sheets("November1").Range("J2:J" & dLastRow)
Set rPen = Sheets("Penalties").Cells(i, pLastRow)[/b]

' Loop through the data import sheet to get consumption values
For i = 2 To pLastRow

'Lookup the consumption value on the November1 sheet

    ConsValue = [b]xl.Index(rNovK, xl.Match(rPen, rNovJ, 0))[/b]
    
    [b]Debug.Print[/b] "Consumption = " & ConsValue & " Units"

' Continue loop
Next i

End Sub

BTW, these sterile range variables, rNovJ, rNovK, rPen would be much improved to use something meaningful. HOWEVER, the OP has not disclosed anything more meaningful about his/her application. Just the fact that one sheet is named "November1" raises specters of non-normal data storage structures

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Not that this will speed up your application, but...
MsgBox has to be clicked on every time (which requires 'babysitting' your code). Which, as Skip pointed out, could be replaced with Debug.Print But Debug.Print also has its limitations, like the number of lines displayed in the Immediate Window.
You may just open a simple text file and write every Consumption value into it.

Just a suggestion...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

>the number of lines displayed in the Immediate
Yep, only 200 lines are retained

 

[renigar]

mintjulep
I don't know much about arrays other than being aware they can be used. I'll have to do some more reading now.

Skip
Thanks for the code. It throws an error on your Set rPen. Looks like the Cells reference is off. And the i is before the loop. Even though I can spot that I still don't know how to make it work. The more I know, the more I know I don't know.

On the msgbox I was only going to loop a couple of times with it. If it worked then I would set it up in the main code with no message box.

 

[SkipVought]

A careless dumb mistake

Set rPen = Sheets("Penalties").Cells([b]pLastRow, 75[/b])

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[renigar]

Skip,
Thank you, but I still couldn't get your example to work. With some reading and changes I got this section of the code to work. I have posted below just the relevant section of code for anyone who is interested. Assume Option Explicit, everything dimmed and so on. With these changes the code now processes the same test set of data in 3.28 minutes. I'm quite impressed considering what my first attempt took. Thanks to everyone for comments, input and examples.

' Loop through the data import sheet to get consumption values
For i = 2 To dLastRow

' Populate variables from the data import sheet
dAcctCID = Sheets(dMonth).Range("J" & i).Value    ' Combined Acct + CID from Data Import sheet
ConsVal = Sheets(dMonth).Range("K" & i).Value     ' Consumption value

' Look for a match to the Data Import sheet AcctCID on the
' Penalties sheet, return position, row count from row 7 start
'  Check if there is no match to the AcctCID, if no match go to next item
    If IsError(Application.Match(Sheets(dMonth).Range("J" & i).Value, Sheets("Penalties").Range("BW7:BW" & pLastRow), 0)) _
        Then GoTo Nexti
' If there is a match assign row position to varible
    X = Application.Match(Sheets(dMonth).Range("J" & i).Value, Sheets("Penalties").Range("BW7:BW" & pLastRow), 0)

' Add 6 (number of header rows)to X to align with actual row numbers
ConsRow = X + 6

' Input the consumption value on the  Penalties sheet
Range(cMonth & ConsRow).Value = ConsVal

' Continue here if an error occurs because of no AcctCID match
Nexti:

' Continue loop
Next i

 

[Andrzejek]

I would avoid [tt]GoTo[/tt]'s, except for [tt]On Error GoTo SomeErrorHandler[/tt]
Since [tt]IsError()[/tt] function returns a Boolean, I would suggest:

For i = 2 To dLastRow
   ...
   If [red]Not[/red] IsError(Application...
      X = Application...
      ...
   End If
Next i

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

My 2¢ concerning "or alternative" in title:
I'm not sure what excel version you use, but if 2016+ or 2013 with com add-in if accepted, then Power Query is an alternative. This may require restructuring workbook (two input tables, one output table, refreshing data manually or by code), but this size of data should be recalculated in single seconds.
Two input tables should be converted to structured tables and introduced to PQ environment by connection only queries. Next merge queries and choose join type, expand merged table, choose required fields and locate output. All steps in single query are semi-automatic and are recorded.


combo

 

[renigar]

Thanks Andy,
I'll make that change.

Combo,
Thanks for the tip. That would be a great improvement. Unfortunately in my organization we are using a mix of different versions of office. I have 2010 on my Desktop and I would have to check on what versions others who may use the workbook have.