I have found that my code causes my Excel to 'not respond' when it gets to this for loop,
For thisRow = 2 To lastWSrow(outWS). I'm guessing this is because I am writing cell by cell and the data is too much for excel to handle.
My code essentially does 55 of vlookups on sheet2. It would be similar to doing
vlookup(c2&y2,Sheet1!I:J,2,false) but manually it takes long to do.
Could anyone look at this loop and help me optimize this area? I have left only the part needed adjustment. I have put comments on each line to provide description.
outws = sheet2
For thisRow = 2 To lastWSrow(outWS) '2 to last row of sheet2 For thisCol = 1 To UBound(mappings, 1) 'create unique key thisScen = outWS.Cells(thisRow, posIDcol).Value & "|" & mappings(thisCol, 1) 'search thisDataRow = findInArrCol(thisScen, 1, scenData) 'write to sheet2 If thisDataRow = 0 Then If outWS.Cells(thisRow, posUnitsCol).Value <> 0 Then 'missing scenario outWS.Cells(thisRow, mappings(thisCol, 3)).Value = "No data" outWS.Cells(thisRow, mappings(thisCol, 3)).Value = 0 End If Else If mappings(thisCol, 1) = "irpv01|PV01_Swap_1M" Then outWS.Cells(thisRow, PV01_1MCol).Value = outWS.Cells(thisRow, PV01_1MCol).Value + scenData(thisDataRow, 2) * scenData(thisDataRow, 3) 'since map PV01_0D to PV01_1m already, sum PV01_1M to that column in database. Else outWS.Cells(thisRow, mappings(thisCol, 3)).Value = scenData(thisDataRow, 2) * scenData(thisDataRow, 3) 'need to scale by position units to get correct risk End If End If Next thisCol Next thisRow
Public Function findInArrCol(matchVal As Variant, matchCol As Long, sortedArr() As Variant, Optional nearest As String) As Long Dim low As Long, mid As Long, high As Long findInArrCol = 0 low = 1 high = UBound(sortedArr, 1) Do While low <= high mid = (low + high) / 2 If sortedArr(mid, matchCol) = matchVal Then findInArrCol = mid Exit Function ElseIf sortedArr(mid, matchCol) < matchVal Then low = mid + 1 Else high = mid - 1 End If Loop If findInArrCol = 0 Then If nearest = "lessThan" Then findInArrCol = WorksheetFunction.Max(high, 1) End If If nearest = "greaterThan" Then findInArrCol = WorksheetFunction.Min(low, UBound(sortedArr, 1)) End If End If End Function