How can I make VBA loop through a column and for every cell index-match + copy the value from an auxiliary sheet?

[klovink001]

Hi everyone,

Im relatively new to VBA, and struggling a bit with the syntax. Hope someone can help!

What im trying to do is this: I have 3 sheets im working with, lets call them sheet1, sheet2 and sheet3. Sheet 1 is input data in which I want to look up a value, for every row in sheet 2. Sheet 3 is the auxiliary (Aux) sheet where I want to do the index-match, and afterwards copy the value to the relevant row on sheet 2. Sheet 2 has over 40.000 rows, so I let i run untill 50.000 to be sure.

This is what I have now:

 Sub Loop3()'
' Loop3 Macro
'

'
Dim i As Integer
Dim startrow As Integer


startrow = 10
i = 0


Application.ScreenUpdating = False


Do While i < 50000
    Worksheets("Aux").Activate
    Worksheets("Aux").Range("G7").Select
    ActiveCell.ClearContents
    ActiveCell.FormulaR1C1 = _
        "=INDEX('Sheet1'!R10C2:R4403C6,MATCH('Sheet2'!R[3+i]C[-5],'Sheet1'!R[3]C[-5]:R[4396]C[-5],0),4)"
    Worksheets("Aux").Range("G7").Select
    Selection.Copy
    Worksheets("Sheet2").Range("AU" & startrow + i).Select
    ActiveCell.Paste
    i = i + 1
    Loop
    
End Sub

Unfortunately im getting the following error: 'Run-time error '1004': Application defined or object defined error'. The script breaks at the line with the INDEX-formula.

This might be a relatively common mistake im making, but again Im not yet experienced is this business. Any ideas?

 

[Fluff]

Untested, but try

ActiveCell.FormulaR1C1 = _
        "=INDEX('Sheet1'!R10C2:R4403C6,MATCH('Sheet2'!R[" & 3 + i & "]C[-5],'Sheet1'!R[3]C[-5]:R[4396]C[-5],0),4)"

 

[klovink001]

Thanks! that seems to be working.

The macro is still very slow though, it takes about 2 mins for it to loop through 100 rows. Could that have something to do with the way the INDEX MATCH formula is written now? Considering I have about 40k rows, is there a way to make the script faster?

 

[Fluff]

I don't understand why you are putting the formula into the Aux sheet & then copying to sheet2.
Why not just put the formula straight into sheet2?

 

[klovink001]

Because that way I would get an INDEX-MATCH formula 40k+ times in my already 80mb+ excel workbook. Im trying to reduce the size by including some macros that do the calculation. So: I created an aux sheet where in just one cell the Index match value is calculated for one row, copied to the relevant row in sheet2, and then the macro loops to the next row. That way sheet 2 just holds hard macro output values and no formulas.

 

[Fluff]

Does this do what you want

 Sub klovink001()
   Dim UsdRws As Long
   
   With Worksheets("Sheet2")
      UsdRws = .Range("B" & Rows.Count).End(xlUp).Row
      With .Range("AU10:AU" & UsdRws)
         .FormulaR1C1 = "=INDEX('Sheet1'!R10C2:R4403C6,MATCH('Sheet2'!RC2,'Sheet1'!R10C2:R4403C2,0),4)"
         .Value = .Value
      End With
   End With
End Sub

 

[klovink001]

Excellent! That just took 99,99% of the time off! Thanks a lot

 

[Fluff]

You're welcome & thanks for the feedback