Writing Macros in Excel
 

Am trying to write a Macro that looks at three fields
(cells) and gives me the Minimum, Maximum, and Average
into another specified field, i.e. Cells(i, 7).Value = Min
(Cells(j, 2)(k, 2)). Get error message that Min is an
undefined Sub or Function. How do I calculate the Min?

Writing Macros in Excel
 

-----Original Message-----
Am trying to write a Macro that looks at three fields
(cells) and gives me the Minimum, Maximum, and Average
into another specified field, i.e. Cells(i, 7).Value = Min
(Cells(j, 2)(k, 2)). Get error message that Min is an
undefined Sub or Function. How do I calculate the Min?
.


Here is how I ended up making it work...
Range("I" & y).FormulaR1C1 = "=MAX(RC[9]:RC[21])" 'MaxQty

Writing Macros in Excel
 

In the VBA macros, you must use
Application.worksheetfunction.Min(argument)

If you do this sort of thing a lot, you can do
Dim wsf as worksheetfunction
Set wsf as Application.worksheetfunction
......
Cells(i,7) = wsf.Min(range(cells(j,2),cells((K,2)))

I confess I have not seen this syntax before. Do you know that it works?
(Cells(j, 2)(k, 2)).
Do you mean Min (Cells(j, 2),cells(k, 2)). (just 2 cells compared)

Stephen M. Rasey
WiserWays, LLC
Houston, TX
http://excelsig.org


"Shannon" wrote in message
...
Am trying to write a Macro that looks at three fields
(cells) and gives me the Minimum, Maximum, and Average
into another specified field, i.e. Cells(i, 7).Value = Min
(Cells(j, 2)(k, 2)). Get error message that Min is an
undefined Sub or Function. How do I calculate the Min?

Writing Macros in Excel
 

Thank you for your help. To answer your question no, I
do not know that the syntax I used works. Prior to the
Do loop I declared i=2, j=2 and k=4. I need to find the
Min, Max and Average of values in three cells (i.e. Cell
(2, 2)(3, 2) and (4, 2)). Cell (5, 2) is blank.
However I need to repeat the Min, Max, Average starting
again at Cell (6, 2) and so on until it comes to the
value of "i" hits an empty cell. I need to put it in a
loop to keep moving down the page, because this argument
must repeat itself until all the cells are calculated.
Therefore, I will be saying i=i+4, j=j+4, and k=k+4
within the loop while Cells(i, 4) Not IsEmpty.

Shannon

-----Original Message-----
In the VBA macros, you must use
Application.worksheetfunction.Min(argument)

If you do this sort of thing a lot, you can do
Dim wsf as worksheetfunction
Set wsf as Application.worksheetfunction
......
Cells(i,7) = wsf.Min(range(cells(j,2),cells((K,2)))

I confess I have not seen this syntax before. Do you
know that it works?
(Cells(j, 2)(k, 2)).
Do you mean Min (Cells(j, 2),cells(k, 2)). (just 2
cells compared)

Stephen M. Rasey
WiserWays, LLC
Houston, TX
http://excelsig.org


"Shannon" wrote in
message
...
Am trying to write a Macro that looks at three fields
(cells) and gives me the Minimum, Maximum, and Average
into another specified field, i.e. Cells(i, 7).Value =
Min
(Cells(j, 2)(k, 2)). Get error message that Min is an
undefined Sub or Function. How do I calculate the Min?


.

Writing Macros in Excel
 

It is hard to tell whether you want to move down rows or move across
columns. you say i=2,j=2, k=4 then refer to cells
(2,2),(3,2),(4,2),(5,2)
I will assume you want i=2, j=3, k = 4 (although you can work with just i)
and loop down the rows, every 4th row

Sub WriteStats()
dim i as long
i = 2
do while not isempty(cells(i,2))
cells(i,3).Value = Application.Min(cells(i,2).Resize(3,1))
cells(i+1,3).Value = Application.Max(cells(i,2).Resize(3,1))
cells(i+2,3).Value = Application.Average(cells(i,2).Resize(3,1))
i = i + 4
Loop
End Sub

test on a copy of your worksheet.
--
Regards,
Tom Ogilvy

"Shannon" wrote in message
...
Thank you for your help. To answer your question no, I
do not know that the syntax I used works. Prior to the
Do loop I declared i=2, j=2 and k=4. I need to find the
Min, Max and Average of values in three cells (i.e. Cell
(2, 2)(3, 2) and (4, 2)). Cell (5, 2) is blank.
However I need to repeat the Min, Max, Average starting
again at Cell (6, 2) and so on until it comes to the
value of "i" hits an empty cell. I need to put it in a
loop to keep moving down the page, because this argument
must repeat itself until all the cells are calculated.
Therefore, I will be saying i=i+4, j=j+4, and k=k+4
within the loop while Cells(i, 4) Not IsEmpty.

Shannon

-----Original Message-----
In the VBA macros, you must use
Application.worksheetfunction.Min(argument)

If you do this sort of thing a lot, you can do
Dim wsf as worksheetfunction
Set wsf as Application.worksheetfunction
......
Cells(i,7) = wsf.Min(range(cells(j,2),cells((K,2)))

I confess I have not seen this syntax before. Do you
know that it works?
(Cells(j, 2)(k, 2)).
Do you mean Min (Cells(j, 2),cells(k, 2)). (just 2
cells compared)

Stephen M. Rasey
WiserWays, LLC
Houston, TX
http://excelsig.org


"Shannon" wrote in
message
...
Am trying to write a Macro that looks at three fields
(cells) and gives me the Minimum, Maximum, and Average
into another specified field, i.e. Cells(i, 7).Value =
Min
(Cells(j, 2)(k, 2)). Get error message that Min is an
undefined Sub or Function. How do I calculate the Min?


.

Writing Macros in Excel
 

Thank you again. Could you please tell me what the
purpose of the (.Resize(3, 1)) command is?

Shannon

-----Original Message-----
It is hard to tell whether you want to move down rows or
move across
columns. you say i=2,j=2, k=4 then refer to cells
(2,2),(3,2),(4,2),(5,2)
I will assume you want i=2, j=3, k = 4 (although you can
work with just i)
and loop down the rows, every 4th row

Sub WriteStats()
dim i as long
i = 2
do while not isempty(cells(i,2))
cells(i,3).Value = Application.Min(cells(i,2).Resize
(3,1))
cells(i+1,3).Value = Application.Max(cells(i,2).Resize
(3,1))
cells(i+2,3).Value = Application.Average(cells
(i,2).Resize(3,1))
i = i + 4
Loop
End Sub

test on a copy of your worksheet.
--
Regards,
Tom Ogilvy

"Shannon" wrote in
message
...
Thank you for your help. To answer your question no, I
do not know that the syntax I used works. Prior to the
Do loop I declared i=2, j=2 and k=4. I need to find the
Min, Max and Average of values in three cells (i.e. Cell
(2, 2)(3, 2) and (4, 2)). Cell (5, 2) is blank.
However I need to repeat the Min, Max, Average starting
again at Cell (6, 2) and so on until it comes to the
value of "i" hits an empty cell. I need to put it in a
loop to keep moving down the page, because this argument
must repeat itself until all the cells are calculated.
Therefore, I will be saying i=i+4, j=j+4, and k=k+4
within the loop while Cells(i, 4) Not IsEmpty.

Shannon

-----Original Message-----
In the VBA macros, you must use
Application.worksheetfunction.Min(argument)

If you do this sort of thing a lot, you can do
Dim wsf as worksheetfunction
Set wsf as Application.worksheetfunction
......
Cells(i,7) = wsf.Min(range(cells(j,2),cells((K,2)))

I confess I have not seen this syntax before. Do you
know that it works?
(Cells(j, 2)(k, 2)).
Do you mean Min (Cells(j, 2),cells(k, 2)). (just 2
cells compared)

Stephen M. Rasey
WiserWays, LLC
Houston, TX
http://excelsig.org


"Shannon" wrote
in
message
...
Am trying to write a Macro that looks at three fields
(cells) and gives me the Minimum, Maximum, and
Average
into another specified field, i.e. Cells(i, 7).Value
=
Min
(Cells(j, 2)(k, 2)). Get error message that Min is
an
undefined Sub or Function. How do I calculate the
Min?


.



.

Writing Macros in Excel
 

Have you looked in vba HELP for RESIZE?

--
Don Guillett
SalesAid Software

"Shannon" wrote in message
...
Thank you again. Could you please tell me what the
purpose of the (.Resize(3, 1)) command is?

Shannon

-----Original Message-----
It is hard to tell whether you want to move down rows or
move across
columns. you say i=2,j=2, k=4 then refer to cells
(2,2),(3,2),(4,2),(5,2)
I will assume you want i=2, j=3, k = 4 (although you can
work with just i)
and loop down the rows, every 4th row

Sub WriteStats()
dim i as long
i = 2
do while not isempty(cells(i,2))
cells(i,3).Value = Application.Min(cells(i,2).Resize
(3,1))
cells(i+1,3).Value = Application.Max(cells(i,2).Resize
(3,1))
cells(i+2,3).Value = Application.Average(cells
(i,2).Resize(3,1))
i = i + 4
Loop
End Sub

test on a copy of your worksheet.
--
Regards,
Tom Ogilvy

"Shannon" wrote in
message
...
Thank you for your help. To answer your question no, I
do not know that the syntax I used works. Prior to the
Do loop I declared i=2, j=2 and k=4. I need to find the
Min, Max and Average of values in three cells (i.e. Cell
(2, 2)(3, 2) and (4, 2)). Cell (5, 2) is blank.
However I need to repeat the Min, Max, Average starting
again at Cell (6, 2) and so on until it comes to the
value of "i" hits an empty cell. I need to put it in a
loop to keep moving down the page, because this argument
must repeat itself until all the cells are calculated.
Therefore, I will be saying i=i+4, j=j+4, and k=k+4
within the loop while Cells(i, 4) Not IsEmpty.

Shannon

-----Original Message-----
In the VBA macros, you must use
Application.worksheetfunction.Min(argument)

If you do this sort of thing a lot, you can do
Dim wsf as worksheetfunction
Set wsf as Application.worksheetfunction
......
Cells(i,7) = wsf.Min(range(cells(j,2),cells((K,2)))

I confess I have not seen this syntax before. Do you
know that it works?
(Cells(j, 2)(k, 2)).
Do you mean Min (Cells(j, 2),cells(k, 2)). (just 2
cells compared)

Stephen M. Rasey
WiserWays, LLC
Houston, TX
http://excelsig.org


"Shannon" wrote
in
message
...
Am trying to write a Macro that looks at three fields
(cells) and gives me the Minimum, Maximum, and
Average
into another specified field, i.e. Cells(i, 7).Value
=
Min
(Cells(j, 2)(k, 2)). Get error message that Min is
an
undefined Sub or Function. How do I calculate the
Min?


.



.