I am using vba code to reset my equations in my spreadsheet. The equation is just pulling data from another worksheet, and every once and a while, I have more data one day than the day before. I created a for loop to loop through all of the cells to update the data to have the correct row number from the other spreadsheet. I created 2 functions, one that is matchEff2, which finds a value in the active sheet, and returns the column number from where the data is, and the second which converts that number to a letter.
I have created this for loop, and it runs when I only have one or two nested functions using the index, but with three nested loops it gives me a runtime error 91. I have used the same line of code and susbtituted an integer in, and it runs as I expected it would.
Currently, I have tried slowling building up the nested loops, to see when I get the error, and I have tried using integers instead of indices as my solutions. I currently have a few debug.print to verify the information that I am putting into the loops.
If Range("L5").Value <> Workbooks("UH-60M Hours.xlsx").Worksheets("UH-60M Pivot").Range(ConvertToLetter(matchEff2(Workbooks("UH-60M Forecast.xlsm").Worksheets("Pos 01M").Range("L1"))) & matchAOS(Range("B5"))) Then
For j = 12 To 35
For i = startRow - 1 To lastRow - 1
Range(ConvertToLetter(j) & i).formula = "='[UH-60M Hours.xlsx]UH-60M Pivot'!" & ConvertToLetter(matchEff2(Range(ConvertToLetter(j) & "1"))) & i + 1
Next i
Next j
End If
Bookmarks