For loop and change a variable

Hi All,

I have this for loop

For i = 2 To SheetNumber
    If Sheets(i).Range("X2") = 2 Then
        Dog = Dog + (Sheets(i).Range("F10").Value - Sheets(i).Range("B9").Value)
        Counter = Counter + 1
    End If
Next
sheets(1).range("B11").value = Dog

I am wondering if there was a way to change the variable Dog each time in the loop. I think i should be able to do this with an array but i cannot seem to get it. I believe my new code would look something like this.

Arr = Array("Dog","Cat","Mouse")
for j = LBound(Arr) to UBound(Arr)
For i = 2 To SheetNumber
    If Sheets(i).Range("X2") = 2 Then
        j = j + (Sheets(i).Range("F10").Value - Sheets(i).Range("B9").Value)
        Counter = Counter + 1
    End If
Next
Next
sheets(1).range("B11").value = Dog & " Dogs," & Cat & " Cats," & Mouse & " Mice."

I am not sure if it is something as simple as taking the "" out of the array or what. Is there some way to change a variable each time the for loop is run? Any help is appretiated. Thanks

What do you want to change it to?

I have edited my original post, to say j instead of Dog. In cell B2 there is a data validation drop down that relates to X2, if x2 is 2 then the data validation is dog, 3 is cat and 4 is mouse. so my real code looks like this, just a little different than before, please let me know if i can explain further

count = 2
Arr = Array("Dog","Cat","Mouse")
for j = LBound(Arr) to UBound(Arr)
For i = 2 To SheetNumber
    If Sheets(i).Range("X2") = count Then
        j = j + (Sheets(i).Range("F10").Value - Sheets(i).Range("B9").Value)
        Counter = Counter + 1
    End If
Next
count = count + 1
Next
sheets(1).range("B11").value = Dog & " Dogs," & Cat & " Cats," & Mouse & " Mice."

Every page from sheet 2 to sheetnumber is the same in terms of content, but the user can have different values per page in the data validation menu. That is why i need to loop through the pages and check to see if the number in x2 is 2, 3, or 4. I would like to loop through and check for 2 and put that in dog, then check for 3 and put in cat, then 4 and put in mouse.

I'm a little confused.

You say it's Dog you want to change, but you don't actually do anything with Dog in the code.

Also, if you want to change it how so you want to change it?

What is the code meant to do anyway?

It seems to be counting something, but what?

I am sorry for the confusion, it is sometimes hard to change things to give a good example. I will try to write out what i am trying to do rather than give code.

Step one is to look through all the pages in the workbook and determine what if any data validation is chosen. The data validation is in cell F2 and it records a number into cell X2, that relates to the value the user has chosen.

I need to go through every page in a varying page workbook (SheetNumber) and look at cell X2 to see what the user has chosen. Then i need the program to average cell F10 in all the worksheets that are similar. Basically an averageif.

The struggles are that the user can choose one of 9 things in the drop down menu and i need to average all like things. That is why i was using a loop to go through all the pages and look at Cell X2, if it was 2 then i wanted to count the number in F10 and store it in Dog, then counter would keep a runnning total of all of the pages that X2 value was 2. as the loop continues i would be adding up all of the F10's that have a 2 in X2 and divide that number by counter. this would give me in essence an average of the F10's with a 2 in X2. Then i need all of the F10's that have a 3 in X2 and divide them by the total pages that have a three in X3.

I hope this makes sense. Really i am just calculating a running averageif. Thanks for your interest and any help you can give me.

I think I kind of get the idea.

Could you attach a sample workbook?

Norie,

Is there anything that you can do for me? Thanks for the help.

I did some more research online and i believe that you cannot change variables in a loop, therefore since Norie could not answer my question i just used 18 different variables in my loop and am closing the thread, i know that this is not efficient but is all i could think to do.

amotto

Sorry for not gettting back to you.

Are you sure you actually need all those variables?

Couldn't you store the running average/total for each category, eg Dog, Cat, in cells on a summary sheet?

As you loop through the worksheets you update the relevant cell on the summary sheet.

Or alternatively have 2 arrays, one for the categories and one for the averages.

Then as you loop through the worksheets you lookup the selected category in the category array and update the correspinding item in the average array.