Macro to create variable range sizes to be used in an averaging function

[ChemE]

So I am very new to VBA, I have some experience in C++, but it was along time ago. I am trying to devise a code that will allow me to find a range based off one column where the values will progress in grouped values, ie. 1,1,1,2,2,2,2,2,2,2,3,4,4,4,4,5,5,5, etc. This range would then be applied to other columns to average those values and placed on another sheet. my experience with C++, for what I remember, makes me believe this is possible, however, I have been unsuccessful. I believe it is just my lack of knowledge of the language that is really hindering me. I have look over the internet finding examples of things similar to what I am looking to do, however, it never seems to work. I have tried For Loops, If Thens, looked through most of the functions I could find in excel but am still coming up blank.

Any help would be greatly appreciated.

tl;dr trying to select a range based off identical values in a column

[Bob Phillips]

I would just use an array formula, something like this

=AVERAGE(IF(A2:A20=1,C2:C20))

and so on.

[ChemE]

would that select a range containing only the value 1? if say A2:A20 had either 1s or 2s in the cells? Because I am trying to avoid having to count the number of cells that contain the same value as this same style macro would be used for my different workbooks, all of which propabaly 5000+ cells, some reaching into the 20000s

[ChemE]

I think maybe the best way to get my question across is this way:

Col. A contains a date value say 39803.45 and up in random increments from 0.2 to 6, I have already simplified the dates values so that they rounded down such that A now contains multiple 39803s, 40034s and such, but the quantity of these values varies and not all values between start and end are present. I need to make day averages of the values in lets say Col. B-D. So the jest of the code I am trying to create is this:

i = row
A= Col (therefore Ai = Cell in worksheet)
j = i+1
if Ai.Value < Aj.Value
then average(Bi:B(j-1))
average(Ci:C(j-1))
average(Di: D(j-1))
else
next j (ie j+1, j+2, j+3, etc. until Ai<Aj)

I know what I said isn't VBA but it was the best way I could explain it in a "form" of code, I hope this better explains my intentions

[Bob Phillips]

would that select a range containing only the value 1? if say A2:A20 had either 1s or 2s in the cells? Because I am trying to avoid having to count the number of cells that contain the same value as this same style macro would be used for my different workbooks, all of which propabaly 5000+ cells, some reaching into the 20000s

Yes it would, that is what the IF does. Try it and see.

[ChemE]

I tried it and from what i can tell, the functions like this:

if a majority of the cells in A2:A20 contain 1 then average all of C2:C20, but I want it to discriminate such that if cells A2:A18=1 while looking at A2:A20 then average C2:C18

[ChemE]

I figured something out, thanks for the help xld

I actually needed to use =AVERAGEIF()

[Bob Phillips]

That is exactly the same as the array =AVERAGE(IF(A2:A20=1,C2:C20))
as I gave you.

[ChemE]

Well apparently not according to Excel 2003-2007, but honestly I do thank you b/c even if that wasn't working, whether my fault or not, it did help me find the solution i was looking for

Thanks again