Using variable addresses in vba

J

JP

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
 
C

cucchiaino

JP said:
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.
Click to expand...

EXAMPLE:

....

measurement = 3500
idmachine = 12

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

....
 
J

JP

EXAMPLE:

...

measurement = 3500
idmachine = 12

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

...
Click to expand...
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
 
C

cucchiaino

JP said:
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?
Click to expand...

No, you won't need it


bye
 
Top