Hi

Here is a problem that has kept me scratching my head for some time.
I have two dynamically changing columns of data in columns A and B.
For each column, the rows are incremented using variable MEASUREMENT
for A and B in vba. There are 20 machines whose numbers appear in A
and their corresponding measurements appear in B. The columns contain
many measurements for each machine.

I would like to accomplish the following example in vba using the
variable MEASUREMENT.

Example: For machine # 12 in column A, (range = A1:A(MEASUREMENT)),
find the MAX of its corresponding measurements in column B (range =
B1:B(MEASUREMENT)) and save it in cell C1.

I would like to accomplish this without using volatile functions like
OFFSET and COUNT. Any help is appreciated as I am running out of
ideas.

Thanks

JP

Using Windows XP, Excel 2003

"JP" ha scritto nel messaggio
...
Hi

Here is a problem that has kept me scratching my head for some time.
I have two dynamically changing columns of data in columns A and B.
For each column, the rows are incremented using variable MEASUREMENT
for A and B in vba. There are 20 machines whose numbers appear in A
and their corresponding measurements appear in B. The columns contain
many measurements for each machine.

I would like to accomplish the following example in vba using the
variable MEASUREMENT.

Example: For machine # 12 in column A, (range = A1:A(MEASUREMENT)),
find the MAX of its corresponding measurements in column B (range =
B1:B(MEASUREMENT)) and save it in cell C1.

I would like to accomplish this without using volatile functions like
OFFSET and COUNT. Any help is appreciated as I am running out of
ideas.


EXAMPLE:

....

measurement = 3500
idmachine = 12

Range("C1").FormulaArray = "=MAX((RC[-2]:R[" & measurement & "]C[-2]=" &
idmachine & ")*(RC[-1]:R[" & measurement & "]C[-1]))"

....

On Sat, 15 May 2004 05:18:30 GMT, "cucchiaino" wrote:


EXAMPLE:

...

measurement = 3500
idmachine = 12

Range("C1").FormulaArray = "=MAX((RC[-2]:R[" & measurement & "]C[-2]=" &
idmachine & ")*(RC[-1]:R[" & measurement & "]C[-1]))"

...



Thanks cucchiaino

As I don't have access to the file over the weekend, I have to try it
on Monday. Would this leave a value in the cell C1 or a formula?
Also, will I need to change the reference style to R1C1 for this to
work?

Take care,

JP

"JP" ha scritto nel messaggio
...
On Sat, 15 May 2004 05:18:30 GMT, "cucchiaino" wrote:

As I don't have access to the file over the weekend, I have to try it
on Monday.
Would this leave a value in the cell C1 or a formula?

Range("C1")=Range("C1")


Also, will I need to change the reference style to R1C1 for this to
work?


No, you won't need it


bye

On Sun, 16 May 2004 18:02:30 GMT, "cucchiaino" wrote:


"JP" ha scritto nel messaggio
.. .
On Sat, 15 May 2004 05:18:30 GMT, "cucchiaino" wrote:

As I don't have access to the file over the weekend, I have to try it
on Monday.
Would this leave a value in the cell C1 or a formula?

Range("C1")=Range("C1")


Also, will I need to change the reference style to R1C1 for this to
work?


No, you won't need it


bye

cucchiaino:

Thanks again, it worked perfectly.