Problems with indicies in For loops, it works with integers but not the index.

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.

Please Login or Register  to view this content.

David,

I just tried that, and the result was that is does work with my functions that convert integers into letters. Unfortunately there are places where the object going into the function is an integer, and not a long. Is from values within my spreadsheet.

Any other thoughts?

Mattie

The posted code does not run, I get the runtime error 91 when going through the code. Since the last post I have determined that it runs through the first column, but does not continue on to the next column. The only time i have been able to make it continue through the loop is when I replaced the last j with an integer as seen below, when ConvertToLetter(12).

Please Login or Register  to view this content.

Shg,

I tried importing your code and using it, and I am still coming up with problems about the code going to the next column. It will adjust all of the data in the first column, but does not change the values in the subsequent columns. All help is greatly appreciated!!!!

Mattie

---------- Post added at 08:01 AM ---------- Previous post was at 07:59 AM ----------

David,

Attached are my functions, ConvertToLetter() and matchEff2()

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Shg,

I will look into using long, but there are other parts of my code, that run, which use the ConvertToLetter(). Currently I am using 6 different debug.prints to verify the information that I am putting into my code.

I currently have your code implemented into my macro, and it will still run just through the first column. The ways I have found to get around this are long and tedious and require verifying everytime I ran the macro.

I have continuouly change the value in L5 to make it not match the other value, as well as changing other formulas in the loop. What I know is that it updates the first column, but does not change the next column unless I use .Cells(i+1,matchEff2(Cells(1,12))).Address... or use .Cells(i+1,j+10).Address...