Match and Index function

[MrThor]

Hi, I have a code which almost works fine for me. I have One sheet called Indata were I import values in column A. The number of cells varies between 3000-5000 approximately. My problem is that I put a For function with an i = 2 to 5000. This means that when the function reaches the first empty cell i column A it will fail. Anyone knows how to deal with it?

See code below:

Dim i As LongDim Resultat As Worksheet
Dim Indata As Worksheet
Dim noder As Worksheet
Dim Rg1 As Range, Rg2 As Range, Rg3 As Range, Rg4 As Range, Rg5 As Range


Set Resultat = Sheets("Resultat")
Set Indata = Sheets("Indata")
Set noder = Sheets("Alla noder")




With Application.WorksheetFunction


For i = 2 To 5000 Step 1




    Indata.Cells(i, 2).Value = .Index(noder.Range("B1:B300000"), .Match(Indata.Cells(i, 1), noder.Range("A1:A300000"), 0))
    Indata.Cells(i, 3).Value = .Index(noder.Range("C1:C300000"), .Match(Indata.Cells(i, 1), noder.Range("A1:A300000"), 0))
    Indata.Cells(i, 4).Value = .Index(noder.Range("D1:D300000"), .Match(Indata.Cells(i, 1), noder.Range("A1:A300000"), 0))
    Indata.Cells(i, 5).Value = .Index(noder.Range("E1:E300000"), .Match(Indata.Cells(i, 1), noder.Range("A1:A300000"), 0))


Next i


End With

 

[Fluff]

Try

for i=2 to range("A"&rows.Count).end(xlup).row

 

[MrThor]

Thank you!! This worked perfectly for me

Have a nice day!

 

[Fluff]

You're welcome & thanks for the feedback

 

[MrThor]

Is there any way to make the code work faster? Right now it takes about two minutes

 

[Fluff]

Try

Sub MrThor()
   Dim Cl As Range
   Dim Noder As Worksheet, InData As Worksheet
   
   Set Noder = Sheets("Alla noder")
   Set InData = Sheets("Indata")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Noder.Range("A1", Noder.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 1).Resize(, 4)
      Next Cl
      For Each Cl In InData.Range("A2", InData.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 1).Resize(, 4).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[MrThor]

Thank you again, this reduced the time to like 2 minutes from 15!

 

[Fluff]

You're welcome & thanks for the feedback