Using variable addresses in vba
 

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

Using variable addresses in vba
 

"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]))"

....

Using variable addresses in vba
 

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

Using variable addresses in vba
 

"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

Using variable addresses in vba
 

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.