msfarrar
08-21-2012, 11:02 AM
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
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