Speed up index match vba in long worksheet

[denmccue]

This macro works. Thank you for your previous help
My worksheet starts out with 1000+ rows at start of month, and the For/Next loop takes a while to run through the sheet. Is there a way to speed it up?

Sub SiteNum()

Dim LastRow As Long
Dim x As Long
Dim y As Long

LastRow = Cells(Rows.Count, 3).End(xlUp).Row

For y = 2 To LastRow

Range("H" & y) = "=Index(" & Range("$P$2:$P$284").Address(False, False) & " , Match(" & Range("E" & y).Address(True, False) & " ," & Range("$O$2:$O$284").Address(True, True) & ", 0))"

Next y
End Sub

 

[Fluff]

No need to use a loop, you can do that like

Sub SiteNum()

Dim LastRow As Long

LastRow = Cells(Rows.Count, 3).End(xlUp).Row
Range("I2:I" & LastRow).Formula = "=Index(P$2:P$284, Match(E2,$O$2:$O$284,0))"
End Sub

 

[denmccue]

VBA Code:

Sub SiteNum()

Dim LastRow As Long

LastRow = Cells(Rows.Count, 3).End(xlUp).Row
Range("I2:I" & LastRow).Formula = "=Index(P$2:P$284, Match(E2,$O$2:$O$284,0))"
End Sub

Thank you Fluff, it works like a champ!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[sloany101]

No need to use a loop, you can do that like

Sub SiteNum()

Dim LastRow As Long

LastRow = Cells(Rows.Count, 3).End(xlUp).Row
Range("I2:I" & LastRow).Formula = "=Index(P$2:P$284, Match(E2,$O$2:$O$284,0))"
End Sub

I am attempting something similar , but not sure how to get this code to work for me , i have a personnel database that is used as an index to match personnel company name based on the persons name , the destination sheet is Daily POB and the index sheet is Personnel Info , this is the formula i have in the actual individual cells =IFERROR(INDEX('Personnel Info'!$C$3:$C$100,MATCH(C3,'Personnel Info'!$A$3:$A$100,0)),"") where C3 in the match portion will go up to C52 , the C3:C100 in the index is going to be much larger than 100 but for now i have set to 100 in an attempt to speed up the file opening as it has become a bit slow to open which is why i am attempting to code this in VBA to initiate when the lookup value is changed .

Would this VBA code be what im looking for?

 

[offthelip]

The really fast way to do index match in VBA is not to use the excel functions but to use the VBA dictionary object. try this code onm a copy of you workbook ( untested) It should writethe results in column D

Sub test()
' this shows hte use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
   
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("Personnel Info")
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    Ary = .Range(.Cells(1, 1), .Cells(lastrow, 3)) ' load all of the data from the Personnel sheet in a variatn array
   End With
   For i = 1 To UBound(Ary, 1)
      Dic(Ary(i, 1)) = Ary(i, 3)  ' load all the data into the dictionary. with value the company name
   Next i
   With Worksheets("Daily POB")
      For Each Cl In .Range("C3:C52")
         Cl.Offset(, 1).Value = Dic(Cl.Value) 'this matches the value given by tyhe index C1.value in the dictionary
      Next Cl
   End With
End Sub

 

[sloany101]

The really fast way to do index match in VBA is not to use the excel functions but to use the VBA dictionary object. try this code onm a copy of you workbook ( untested) It should writethe results in column D

Sub test()
' this shows hte use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
  
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("Personnel Info")
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    Ary = .Range(.Cells(1, 1), .Cells(lastrow, 3)) ' load all of the data from the Personnel sheet in a variatn array
   End With
   For i = 1 To UBound(Ary, 1)
      Dic(Ary(i, 1)) = Ary(i, 3)  ' load all the data into the dictionary. with value the company name
   Next i
   With Worksheets("Daily POB")
      For Each Cl In .Range("C3:C52")
         Cl.Offset(, 1).Value = Dic(Cl.Value) 'this matches the value given by tyhe index C1.value in the dictionary
      Next Cl
   End With
End Sub

This worked Beautifully , and i was able to successfully apply to a different range of cells !! Now i have to figure out how to make it run only when the lookup value is changed rather than having to manually run it.

 

[offthelip]

that is very simple just put this code in the Daily POB worksheet code. ( Right click on the tab and select viewcode):

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Range("C3:C52"), Target) Is Nothing Then
  Call test
 End If
End Sub

 

[sloany101]

excellent this works well too , if you dont mind one last thing , how would i add in addition to pull data from the personnel sheet from column g3 and h3 and up to daily pob sheet columns J3 and k3

 

[sloany101]

excellent this works well too , if you dont mind one last thing , how would i add in addition to pull data from the personnel sheet from column g3 and h3 and up to daily pob sheet columns J3 and k3

Nevermind i figured it out , now i have noticed it has sped up the file when opening , but the macro takes a couple of seconds to run and this will be a problem , what if there were a way to make the macro work like index/match formula rather than updating the entire range when a target cell is changed but only update the info for the individual target cell .... if that makes any sense??