Vlookup VBA Alternative

[helpexcel]

Is there a quicker way to use vlookup with VBA. I have a lot of data that I would just use vlookup with, across multiple sheets. Hoping there's a way to speed things up. THANKS!

 

[helpexcel]

Ah, got it. I thought i/j had to match up with the starting rows. I'm still running into an issue where column D on sheet16 ins't pulling over to column E on sheet1.

 

[offthelip]

It is the same problem but with columns this time:

outarr = Range(.Cells(1, 4), .Cells(lastrow2, 5))

For kk = 3 To 4
outarr(i, kk - 1) = inarr(j, kk)
Next kk

Outarr is already defined as matching columnns 4 and 5 ( D and E), but the array is only two columns wide, so the index when writing to it must be 1 and 2
However your code is writing with an index of 2 and 3 ( kk-1)
so change the index to KK -2

 

[helpexcel]

Thank you. So in kk-x, x should always be the number needed to get a starting point of 1? kk = 6 to 10, kk-5?

 

[offthelip]

Yes .
If you load an array from the worksheet then the index will always start at 1, in this case we loaded the array from column 4 and 5 but it could have been 14 and 15 or any two columns , the index into the array will always be 1 and 2.
However be careful if you define the array using a dim statement you can make the indices anything you like ( more or less) e.g.

Dim outarr(0 To 1, 3 To 44)
outarr(1, 3) = "test"
MsgBox outarr(1, 3)

put a breakpoint on the msgbox line and have a look at the outarr array on the locals window and you willl see what I mean

 

[Dracius]

@offthelip,

After a lot of searching, I feel like your answer given here is the best, and most efficient solution for what I'm trying to do. Almost all the other alternatives I've found either query the worksheet on each loop or use the application.worksheet method. I'm trying to modify this however to keep the array size it returns down, and while my method works, I wanted to run it by you to see if you can think of a better way. My table is over 60 columns but I only need data returned from 2 of those.

Again, this code is working flawlessly and efficiently; but curious to see if you either A) Know a better way or B) see a flaw in my approach:

Private Sub Test()

Dim Lastrow, i As Long
Dim Searchfor, Lookup, Result1, Result2 As Variant


Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Searchfor = Cells(1, 62)
Lookup = Range(Cells(1, 1), Cells(Lastrow, 1))
Result1 = Range(Cells(1, 3), Cells(Lastrow, 3))
Result2 = Range(Cells(1, 8), Cells(Lastrow, 8))
For i = 1 To Lastrow
 If Lookup(i, 1) = Searchfor Then
  Cells(2, 62) = Result1(i, 1) & " " & Result2(i, 1)
  Exit For
 End If
Next i


End Sub

I'm also going to see if I can't convert this to a function for single lookups, so I can just specify what I want to lookup, what column it's in, and what column I want the result returned from, as I'm going to have to daisy chain a lot of these together for an API project I'm working on.

Thank you for your write-ups by the way, they were very useful in helping me actually UNDERSTAND how the code works.

 

[offthelip]

Your code looks fine, I would have written it in exactly the same way. However converting it into a function could make your worksheet a bit slow if you use the function a lot. this is because every call to the function will load the lookup range and the two results ranges. so for speed it is much faster to load them once and do all the lookups using the same call. this means calling the subroutine once when you need to recalculate the worksheet.

 

[Dracius]

so for speed it is much faster to load them once and do all the lookups using the same call

That's exactly what I had done. I kept them outside the loop/function and just passed through the values to the function call. Ended up taking a different approach though, here's the final code:

Private Sub Fixer()    
    Dim RowCounter As Long
    For RowCounter = 2 To GetLastRow(ActiveSheet, 1)
        If Cells(RowCounter, 59) = True Then
            Call ChangeTaskDates(Cells(RowCounter, 6), Cells(RowCounter, 1), Cells(RowCounter, 36), Cells(RowCounter, 37))
        End If
    Next RowCounter


End Sub

Public Function ChangeTaskDates(ByRef ProjectID As String, ByRef TaskID As String, ByRef StartDate As String, ByRef EndDate As String) As String

    Dim BaseURL As String: BaseURL = "REDACTED"
    Dim GUID As String: GUID = Worksheets("API").Range("B1")
    Dim RawJSON As String: RawJSON = GetHTTP(BaseURL & GUID & "&projectid=" & ProjectID & "&taskid=" & TaskID & "&startdate=" & Left(StartDate, 10) & "&duedate=" & Left(EndDate, 10) & "&FORMAT=JSON")
    Dim JSON As Object: Set JSON = JsonConverter.ParseJson(RawJSON)
    Dim ErrorDesc As String: ErrorDesc = JSON("results")(1)("ERRORDESCRIPTION")


    If JSON("status") = "fail" Then
        Dim FixedDate As String: FixedDate = Format(SRegEx("(\d+\/\d+\/\d+)", ErrorDesc), "yyyy-mm-dd")
        Call GetHTTP(BaseURL & GUID & "&projectid=" & ProjectID & "&taskid=" & TaskID & "&startdate=" & FixedDate & "&duedate=" & FixedDate & "&FORMAT=JSON")
    End If
    
    Set JSON = Nothing
    
End Function

Private Function GetHTTP(ByVal URL As String) As String
    On Error Resume Next
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", URL, False
        .Send
        GetHTTP = .ResponseText
    End With
End Function

 

[offthelip]

You are still accessing the worksheet in a loop so you can make your code faster by loading the column 59 into an array. like this:

Private Sub Fixer()
    Dim RowCounter As Long
   lrow = GetLastRow(ActiveSheet, 1)
   inarr = Range(Cells(1, 59), Cells(lrow, 59))
   
    For RowCounter = 2 To lrow
        If inarr(RowCounter, 1) = True Then
            Call ChangeTaskDates(Cells(RowCounter, 6), Cells(RowCounter, 1), Cells(RowCounter, 36), Cells(RowCounter, 37))
        End If
    Next RowCounter
End Sub

Depending on how many lines do have "true" in column 59 it could be faster to load the entire sheet into a variant array rather than one column.

 

[PietBom]

I have a much faster Lookup formula which is executed by VBA:
Worksheet(1)Bron consists of a Table starting in A1 and has 2 columns and has 1,000,000 records with a randomized unique Code and in the 2nd column the number of Mhrs per Code. The header of column A is Kode in cell A1
Worksheet(2)Kweries consists of a list of 1,000 Codes from the Table on Bron randomnly which shoud be looked up in the table in Bron and the number of Mhrs need to be returned to column C on Kweries
The Table on Bron should be sorted from A-Z and the codes must be unique.
The list of codes on Kweries can be in randomn order.
Macro Vlook_Up will put 1000 Vlookup formulas in column C of Kweries and will be Calculated to find the Mhrs for each Code in Kweries.
So Excel should do a 1000 Vlookups in a Table of 1,000,000 records.
This used formula will take less then 0,1 second.
I could not believe it the first time, but it is amazing.
the formulas are calculated once and than converted to Values. This way your large Table will not slow down if you leave Automatic Calculation on.
------------------

Sub Vlook_Up2()
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  [C2].FormulaR1C1 = "=IF(VLOOKUP(RC2,Table1,1,1)=RC2,VLOOKUP(RC[-1],Table1,2,1),-999)"
  LR2 = Sheets("Kweries").Range("B" & Rows.Count).End(xlUp).Row
  Range("C2:C" & LR2).FillDown
  Calculate
  Range("C3:C" & LR2).Copy
  Range("C3:C" & LR2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub

 

[Dracius]

You are still accessing the worksheet in a loop so you can make your code faster by loading the column 59 into an array. like this:

In that example, yes, I was, but that's because I had changed the method I was using.


Initially I had this:

Private Sub Fixer()    
    Dim i As Long
    Dim Lastrow As Long: Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    Dim Run As Variant: Run = Range(Cells(1, 59), Cells(Lastrow, 59))
    Dim ProjectID  As Variant: ProjectID = Range(Cells(1, 6), Cells(Lastrow, 6))
    Dim TaskID As Variant: TaskID = Range(Cells(1, 1), Cells(Lastrow, 1))
    Dim StartDate As Variant: StartDate = Range(Cells(1, 36), Cells(Lastrow, 36))
    Dim EndDate As Variant: EndDate = Range(Cells(1, 37), Cells(Lastrow, 37))
    
    For i = 1 To Lastrow
     If Run(i, 1) = True Then
        Call ChangeTaskDates(ProjectID(i, 1), TaskID(i, 1), StartDate(i, 1), EndDate(i, 1))
     End If
    Next i


End Sub

I've incorporated your method now, and declared the variables you've added (lrow & inarr):

Private Sub DateFixer_Click()    
    Dim RowCounter As Long
    Dim lrow As Variant: lrow = GetLastRow(ActiveSheet, 1)
    Dim inarr As Variant: inarr = Range(Cells(1, 59), Cells(lrow, 59))
   
    For RowCounter = 2 To lrow
        If inarr(RowCounter, 1) = True Then
            If Cerberus.ChangeTaskDates(Cells(RowCounter, 6), Cells(RowCounter, 1), Cells(RowCounter, 36), Cells(RowCounter, 37)) = "ERROR" Then Cells(RowCounter, 60) = "ERROR"
        End If
    Next RowCounter


End Sub

Once this is running nightly, there should only ever be 5-10 "True"'s in column 59 and once I limit my Project/Task queries down the table sizes should stay under ~100. I've got an initial backlog to clean up though that is about 200+ tasks.

Side note, and this could just be my ****ty computer, excel was/is crashing a lot when using the variant arrays. Runs fine if I just re-open, don't change anything in the code and just execute it, but if I modify the code, add another array, etc, then try to re-run it excel will crash. When I wasn't using the arrays excel was never crashing on me. Also due to my table size (100 rows x 60 columns) it's hard to tell if storing it in the array is speeding it up by any noticable amount. Is there a function I can use to measure how long the process takes to fully execute so I can benchmark it?

PS. On your naming conventions, what is the "in" on the "inarr" indicating? I assume the "arr" is to indicate it's an array. Would "RunScriptArr" make more sense in my usage case?