1

I'm building a workbook to re-rate products based on proposed rates. I have three tabs, one which rates the products, one which stores the rates for each variable, and another with the product data. The goal of the VBA is to re-rate every customer on the data tab with the new rates. The product data tab is set up the following way: Index, variable 1, variable 2...charge. The rating tab is set up to look up the index and re-rate it based on the variables. The VBA works by running through each index and pasting it to the rating tab. How can I speed up the macro? It looks something like this:

Set wa = ActiveWorkbook.Sheets("Rate")
Set ws = ActiveWorkbook.Sheets("Data")
lRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 to lRow:
Application.ScreenUpdating = False 
wa.Range("L86") = ws.Range("BZ" & i).Value 'this is where the index is copied 
ws.Range("BL" & i) = wa.Range("M65").Value 'this is a new re-rated attribute 
ws.Range("BM" & i) = wa.Range("N65").Value 'this is a new re-rated attribute
Next i
Golffan27
  • 11
  • 2
  • I didn't mean to have the "-1" there. I edited the code to show this. Also, I am not putting the same value into each cell. For each iteration, it re-rates a new product. The cell value changes based on the rating attributes for each product/index. – Golffan27 Mar 02 '21 at 15:18
  • `wa.Range("L86") = ws.Range("BZ" & i).Value` will essential put the last value in that cell as the loop will be quick enough to only see that value after the loop is done. – Scott Craner Mar 02 '21 at 15:22
  • @ScottCraner The loop works correctly now, I just want to speed it up. Each iteration (index) is placed in L86 on the Rate tab, then it rates these attributes and pastes the new charges back to the Data tab. It repeats this process for each i. – Golffan27 Mar 02 '21 at 15:42
  • use variant arrays and do the calculations in the sub routine again using variant arrays. passing data back and forth from vba to the sheet is slow. With what you have said there is no other information we can give, because we do not know what the formula are doing to internalize it into the code itself. – Scott Craner Mar 02 '21 at 15:46
  • @ScottCraner So once the index is placed on the Rate sheet (wa.Range("L86") = ws.Range("BZ" & i).Value), the formulas on this tab rate the products. Each formula is an index/match. It first looks up the rating attributes on the Data tab (index/match) and then looks up the charges associated with each variable (again index/match). The tables with the associated charges are stored on a different tab, called Charges. Once it has all this information, it then pastes the new charges back onto the Data tab (ws.Range("BL" & i) = wa.Range("M65").Value). – Golffan27 Mar 02 '21 at 15:56
  • 2
    as my comment above states. create variant arrays and bulk load everything into them. Then iterate the arrays do the lookup looping other arrays and then load even another array with the correct output and then bulk assign the values back to the worksheet. You will need to try to do that and when you get stuck come back with the refactored code and ask a specific question about how to overcome. – Scott Craner Mar 02 '21 at 15:59
  • You can also always try these general tips: https://stackoverflow.com/a/49514930/4593433 – frankenapps Mar 02 '21 at 16:43

0 Answers0