Index problems in for loops - excel vba

[msfarrar]

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. I have also tried using my indices as long instead of integer, but that made my functions not work, and I need to keep my functions using integers.

Thanks in advance!



'Checks to see if the values correspond
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
'if values do not match, change the row number to the correct one
Range(ConvertToLetter(j) & i).formula = "='[UH-60M Hours.xlsx]UH-60M Pivot'!" & ConvertToLetter(matchEff2(Range_(ConvertToLetter(j) & "1"))) & i + 1
'Finds number of current header in other spreadsheet, and converts it to a letter so that the proper column can be part of the_ formula
Next i
Next j
End If

[patel]

how can I test your macro ?

[CatDaddy]

'Checks to see if the values correspond
If Range("L5").Value <> Workbooks("UH-60M Hours.xlsx").Worksheets("UH-60M Pivot").Cells(matchAOS(Range("B5")), matchEff2(Workbooks("UH-60M Forecast.xlsm").Worksheets("Pos 01M").Range("L1"))).Value Then
For j = 12 To 35
For i = startRow - 1 To lastRow - 1
Cells(i, j).Value = Workbooks("UH-60M Hours.xlsx").Worksheets("UH-60M Pivot").Cells((i + 1), matchEff2(Cells(1, j))).Value
Next i, j
End If

[msfarrar]

Attached are my two spreadsheets, and code. It contains the macro and all of the functions necessary for that sheet. It only allows me to add one sheet at a time, so I will post one with the other spreadsheet.

[msfarrar]

Unfortunately, my other file is much to big to upload it to the system. It also my work computer does not allow use of zipped documents, so I cannot attach the file that way.

[msfarrar]

Changing from range to cells did not prevent the error. :-(