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:
See code below:
Code:
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