Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Writing Macros | Excel Discussion (Misc queries) | |||
writing macros in excel sheet | Excel Worksheet Functions | |||
Writing Macros | Excel Worksheet Functions | |||
Need help writing basic macros in EXCEL.. | Excel Discussion (Misc queries) | |||
Writing 2 macros to sort names and numbers in excel 97 | Excel Programming |