Faster way to loop through cells to multiply vlookup value

Question

Currently I have a for loop that loops through every cell in sheet1 column P to extract a flavor type, uses a vlookup found in a named range called Table from sheet0 to get the price and then multiplies the price by several different numerical columns back in sheet1. It then transfers the multiplied columns to sheet2. Which I transfer back those columns to sheet1 to replace the original values.

The last part I use a with function with copy and paste, which works quickly. This does the first part:

lastrow = wsSheet1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 to lastrow
     erow = wsSheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row

     wsSheet2.Cells(erow, 1) = wsSheet1.Cells(i, 6) * Application.VLookup(wsSheet1.Cells(i, 16), wsSheet0.Range("Table"), 2, False)
     wsSheet2.Cells(erow, 2) = wsSheet1.Cells(i, 7) * Application.VLookup(wsSheet1.Cells(i, 16), wsSheet0.Range("Table"), 2, False)
Next i

This loop goes through roughly 10 times, and it's such a slow process to reference each cell in column P / 16, and multiply the value and transfer to sheet2. I wanted a faster way to do this.

I tried going through this alternative process with the With function where I would just skip the transferring to sheet2, and replace the values within sheet1 -

With wsSheet1.Range("F2:F" & lastrow)
    .Value = Evaluate(.Address & "*" & [VLOOKUP('sheet1'!P2, 'sheet0'!FXTable, 2, FALSE)])
End With

But not sure how to make this work with a reference range that's dynamically changing so the vlookup value wouldn't be fixed at P2 like I have above

Here's a link of What is the most efficient/quickest way to loop through rows in VBA (excel) — warner_sc

Gene Gene · Accepted Answer · 2018-05-26T01:17:20

You are probably looking for something like this:

    lastrow = wsSheet1.Cells(Rows.Count, 1).End(xlUp).Row

    erow = wsSheet2.Cells(Rows.Count, 2).End(xlUp).Row
    Set rT = wsSheet0.Range("Table").Columns(1)

    For i = 2 To lastrow
            Price = rT.Find(wsSheet1.Cells(i, 16), LookIn:=xlValues).Cells(1, 2)
            wsSheet2.Cells(erow + i - 1, 1) = wsSheet1.Cells(i, 6) * Price
            wsSheet2.Cells(erow + i - 1, 2) = wsSheet1.Cells(i, 7) * Price
    Next i

Please let us know how fast it runs this way. Good luck!

Faster way to loop through cells to multiply vlookup value

1 Answers