Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter the smallest amount in row
Hello:
I would like to have a code that enters the smallest number on a row to the rest of the row. So lets say I use the range from A1 to H10. I want that for row 1 if it has in A1 the value 253 and in B1 the value 219 and in C1 the value 198, so the rest of that row (until H1) should have the value 198. And when I'll enter the value 153 in D1, then all the rest after D1 should update to 153 and so on. This should occur in each row in the range. I usually enter the amounts from left to right in the range. Thanks for any help. Art |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter the smallest amount in row
Hi Art,
If I understand your question correctly then place the folowing formula in D1 and copy it across to H1 then copy D1:H1 down. =MIN($A1:C1) -- Regards, OssieMac "art" wrote: Hello: I would like to have a code that enters the smallest number on a row to the rest of the row. So lets say I use the range from A1 to H10. I want that for row 1 if it has in A1 the value 253 and in B1 the value 219 and in C1 the value 198, so the rest of that row (until H1) should have the value 198. And when I'll enter the value 153 in D1, then all the rest after D1 should update to 153 and so on. This should occur in each row in the range. I usually enter the amounts from left to right in the range. Thanks for any help. Art |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter the smallest amount in row
No. I can't use this. I need to the Min in the whole list starting from A1 to
H10. So when I start, and the whole chart is empty all values are zero. Then when I enter the first amount in A1 (lets say 10), then the whole row should be the same since it is the lowest in the row (other than zero). Then when I enter the next number in B1 (lest say 8), then the rest of the cells from C1 to H1 should have the 8. And when I enter a smaller amount in c1 the rest of the row should lower as well. This can't be done with a formaula, as the formula would get ereased when I enter an amount in that cell. The same should happen when I delete one amount, then thwe whole row should update with the smallest amount of that row, including the cell that was just deleted. Thanks. "OssieMac" wrote: Hi Art, If I understand your question correctly then place the folowing formula in D1 and copy it across to H1 then copy D1:H1 down. =MIN($A1:C1) -- Regards, OssieMac "art" wrote: Hello: I would like to have a code that enters the smallest number on a row to the rest of the row. So lets say I use the range from A1 to H10. I want that for row 1 if it has in A1 the value 253 and in B1 the value 219 and in C1 the value 198, so the rest of that row (until H1) should have the value 198. And when I'll enter the value 153 in D1, then all the rest after D1 should update to 153 and so on. This should occur in each row in the range. I usually enter the amounts from left to right in the range. Thanks for any help. Art |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter the smallest amount in row
Right click the sheet tab view code and paste the code. This will change the
values of cells to wards the right...Try and feedback...I am not sure is this what you are looking for....Try and feedback Private Sub Worksheet_Change(ByVal Target As Range) Dim intCol As Integer Dim varMin As Variant Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:H10")) Is Nothing Then varMin = WorksheetFunction.Min(Range("A" & Target.Row & ":H" & Target.Row)) If varMin < 0 Then For intCol = Target.Column To 8 Cells(Target.Row, intCol) = varMin Next End If End If Application.EnableEvents = True End Sub -- If this post helps click Yes --------------- Jacob Skaria "art" wrote: No. I can't use this. I need to the Min in the whole list starting from A1 to H10. So when I start, and the whole chart is empty all values are zero. Then when I enter the first amount in A1 (lets say 10), then the whole row should be the same since it is the lowest in the row (other than zero). Then when I enter the next number in B1 (lest say 8), then the rest of the cells from C1 to H1 should have the 8. And when I enter a smaller amount in c1 the rest of the row should lower as well. This can't be done with a formaula, as the formula would get ereased when I enter an amount in that cell. The same should happen when I delete one amount, then thwe whole row should update with the smallest amount of that row, including the cell that was just deleted. Thanks. "OssieMac" wrote: Hi Art, If I understand your question correctly then place the folowing formula in D1 and copy it across to H1 then copy D1:H1 down. =MIN($A1:C1) -- Regards, OssieMac "art" wrote: Hello: I would like to have a code that enters the smallest number on a row to the rest of the row. So lets say I use the range from A1 to H10. I want that for row 1 if it has in A1 the value 253 and in B1 the value 219 and in C1 the value 198, so the rest of that row (until H1) should have the value 198. And when I'll enter the value 153 in D1, then all the rest after D1 should update to 153 and so on. This should occur in each row in the range. I usually enter the amounts from left to right in the range. Thanks for any help. Art |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter the smallest amount in row
Give this macro a try...
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("A1:H10")) Is Nothing Then Range(Target, Cells(Target.Row, "H")).Value = Target.Value End If Application.EnableEvents = True End Sub To install the macro, right click the tab at the bottom of the worksheet where you want this functionality, select View Code from the popup menu that appears and copy/paste the above code into the code window that appears. That's it... now go back to your worksheet and enter your values into the range A1:H10. -- Rick (MVP - Excel) "art" wrote in message ... Hello: I would like to have a code that enters the smallest number on a row to the rest of the row. So lets say I use the range from A1 to H10. I want that for row 1 if it has in A1 the value 253 and in B1 the value 219 and in C1 the value 198, so the rest of that row (until H1) should have the value 198. And when I'll enter the value 153 in D1, then all the rest after D1 should update to 153 and so on. This should occur in each row in the range. I usually enter the amounts from left to right in the range. Thanks for any help. Art |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter the smallest amount in row
Thanks. But what happens when I have in B1 the amount 8 and I want to change
it to 9, it does not let me, it changes it back to 8. How can I have it change to the lowest amount in the range? "Jacob Skaria" wrote: Right click the sheet tab view code and paste the code. This will change the values of cells to wards the right...Try and feedback...I am not sure is this what you are looking for....Try and feedback Private Sub Worksheet_Change(ByVal Target As Range) Dim intCol As Integer Dim varMin As Variant Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:H10")) Is Nothing Then varMin = WorksheetFunction.Min(Range("A" & Target.Row & ":H" & Target.Row)) If varMin < 0 Then For intCol = Target.Column To 8 Cells(Target.Row, intCol) = varMin Next End If End If Application.EnableEvents = True End Sub -- If this post helps click Yes --------------- Jacob Skaria "art" wrote: No. I can't use this. I need to the Min in the whole list starting from A1 to H10. So when I start, and the whole chart is empty all values are zero. Then when I enter the first amount in A1 (lets say 10), then the whole row should be the same since it is the lowest in the row (other than zero). Then when I enter the next number in B1 (lest say 8), then the rest of the cells from C1 to H1 should have the 8. And when I enter a smaller amount in c1 the rest of the row should lower as well. This can't be done with a formaula, as the formula would get ereased when I enter an amount in that cell. The same should happen when I delete one amount, then thwe whole row should update with the smallest amount of that row, including the cell that was just deleted. Thanks. "OssieMac" wrote: Hi Art, If I understand your question correctly then place the folowing formula in D1 and copy it across to H1 then copy D1:H1 down. =MIN($A1:C1) -- Regards, OssieMac "art" wrote: Hello: I would like to have a code that enters the smallest number on a row to the rest of the row. So lets say I use the range from A1 to H10. I want that for row 1 if it has in A1 the value 253 and in B1 the value 219 and in C1 the value 198, so the rest of that row (until H1) should have the value 198. And when I'll enter the value 153 in D1, then all the rest after D1 should update to 153 and so on. This should occur in each row in the range. I usually enter the amounts from left to right in the range. Thanks for any help. Art |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter the smallest amount in row
Its not bad, however, i want to change it, that if I have in A1 9, in B1 8,
and in c1 I enter 10, it should still stay 8 since there is a lower amount in B1. Thanks so much. "Rick Rothstein" wrote: Give this macro a try... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("A1:H10")) Is Nothing Then Range(Target, Cells(Target.Row, "H")).Value = Target.Value End If Application.EnableEvents = True End Sub To install the macro, right click the tab at the bottom of the worksheet where you want this functionality, select View Code from the popup menu that appears and copy/paste the above code into the code window that appears. That's it... now go back to your worksheet and enter your values into the range A1:H10. -- Rick (MVP - Excel) "art" wrote in message ... Hello: I would like to have a code that enters the smallest number on a row to the rest of the row. So lets say I use the range from A1 to H10. I want that for row 1 if it has in A1 the value 253 and in B1 the value 219 and in C1 the value 198, so the rest of that row (until H1) should have the value 198. And when I'll enter the value 153 in D1, then all the rest after D1 should update to 153 and so on. This should occur in each row in the range. I usually enter the amounts from left to right in the range. Thanks for any help. Art |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter the smallest amount in row
Sorry, I had misread your requirements. Does this do what you want?
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Count = 1 And Not Intersect(Target, _ Range("A1:H10")) Is Nothing Then Range(Target, Cells(Target.Row, "H")).Value = _ WorksheetFunction.Min(Range("A1", Target)) End If Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "art" wrote in message ... Its not bad, however, i want to change it, that if I have in A1 9, in B1 8, and in c1 I enter 10, it should still stay 8 since there is a lower amount in B1. Thanks so much. "Rick Rothstein" wrote: Give this macro a try... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("A1:H10")) Is Nothing Then Range(Target, Cells(Target.Row, "H")).Value = Target.Value End If Application.EnableEvents = True End Sub To install the macro, right click the tab at the bottom of the worksheet where you want this functionality, select View Code from the popup menu that appears and copy/paste the above code into the code window that appears. That's it... now go back to your worksheet and enter your values into the range A1:H10. -- Rick (MVP - Excel) "art" wrote in message ... Hello: I would like to have a code that enters the smallest number on a row to the rest of the row. So lets say I use the range from A1 to H10. I want that for row 1 if it has in A1 the value 253 and in B1 the value 219 and in C1 the value 198, so the rest of that row (until H1) should have the value 198. And when I'll enter the value 153 in D1, then all the rest after D1 should update to 153 and so on. This should occur in each row in the range. I usually enter the amounts from left to right in the range. Thanks for any help. Art |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter the smallest amount in row
Great. Thanks so much for your help. (I guess I can't change a number E.G. in
cell C1 from lower to a higher number if in cell B1 has a lower number than that. So if I have in Cell A1 900 in Cell B1 850 and in cell C1 600 and I want to change C1 to 880, it will not let me and change all the cell from C1 to the end H1 to "850". So in essence if there is a lower amount on the left, it should enter that amount but still let me change any cell to a higher one. Don't know if its possible.) However, Thanks again for this code. Art. "Rick Rothstein" wrote: Sorry, I had misread your requirements. Does this do what you want? Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Count = 1 And Not Intersect(Target, _ Range("A1:H10")) Is Nothing Then Range(Target, Cells(Target.Row, "H")).Value = _ WorksheetFunction.Min(Range("A1", Target)) End If Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "art" wrote in message ... Its not bad, however, i want to change it, that if I have in A1 9, in B1 8, and in c1 I enter 10, it should still stay 8 since there is a lower amount in B1. Thanks so much. "Rick Rothstein" wrote: Give this macro a try... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("A1:H10")) Is Nothing Then Range(Target, Cells(Target.Row, "H")).Value = Target.Value End If Application.EnableEvents = True End Sub To install the macro, right click the tab at the bottom of the worksheet where you want this functionality, select View Code from the popup menu that appears and copy/paste the above code into the code window that appears. That's it... now go back to your worksheet and enter your values into the range A1:H10. -- Rick (MVP - Excel) "art" wrote in message ... Hello: I would like to have a code that enters the smallest number on a row to the rest of the row. So lets say I use the range from A1 to H10. I want that for row 1 if it has in A1 the value 253 and in B1 the value 219 and in C1 the value 198, so the rest of that row (until H1) should have the value 198. And when I'll enter the value 153 in D1, then all the rest after D1 should update to 153 and so on. This should occur in each row in the range. I usually enter the amounts from left to right in the range. Thanks for any help. Art |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter the smallest amount in row
I'm not sure how being able to do that meshes with your earlier posted
criteria... can you clarify? Here is my problem in understanding what you want... you said you wanted a lower number put in a cell to be copied across to the Column H and you said "if I have in A1 9, in B1 8, and in c1 I enter 10, it should still stay 8 since there is a lower amount in B1", but now you say you want "if I have in Cell A1 900 in Cell B1 850 and in cell C1 600 and I want to change C1 to 880"... I just don't understand the rules that are at work here... when can something be changed and when can't it? -- Rick (MVP - Excel) "art" wrote in message ... Great. Thanks so much for your help. (I guess I can't change a number E.G. in cell C1 from lower to a higher number if in cell B1 has a lower number than that. So if I have in Cell A1 900 in Cell B1 850 and in cell C1 600 and I want to change C1 to 880, it will not let me and change all the cell from C1 to the end H1 to "850". So in essence if there is a lower amount on the left, it should enter that amount but still let me change any cell to a higher one. Don't know if its possible.) However, Thanks again for this code. Art. "Rick Rothstein" wrote: Sorry, I had misread your requirements. Does this do what you want? Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Count = 1 And Not Intersect(Target, _ Range("A1:H10")) Is Nothing Then Range(Target, Cells(Target.Row, "H")).Value = _ WorksheetFunction.Min(Range("A1", Target)) End If Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "art" wrote in message ... Its not bad, however, i want to change it, that if I have in A1 9, in B1 8, and in c1 I enter 10, it should still stay 8 since there is a lower amount in B1. Thanks so much. "Rick Rothstein" wrote: Give this macro a try... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("A1:H10")) Is Nothing Then Range(Target, Cells(Target.Row, "H")).Value = Target.Value End If Application.EnableEvents = True End Sub To install the macro, right click the tab at the bottom of the worksheet where you want this functionality, select View Code from the popup menu that appears and copy/paste the above code into the code window that appears. That's it... now go back to your worksheet and enter your values into the range A1:H10. -- Rick (MVP - Excel) "art" wrote in message ... Hello: I would like to have a code that enters the smallest number on a row to the rest of the row. So lets say I use the range from A1 to H10. I want that for row 1 if it has in A1 the value 253 and in B1 the value 219 and in C1 the value 198, so the rest of that row (until H1) should have the value 198. And when I'll enter the value 153 in D1, then all the rest after D1 should update to 153 and so on. This should occur in each row in the range. I usually enter the amounts from left to right in the range. Thanks for any help. Art |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter the smallest amount in row
What I meant to say is that when I enter in C1 880, which is higher than B1,
it should "let" you change it to the higher number of 880, however, the rest of the cells to the right of C1 (D1, Ei, F1 etc.) should be equal to the lowest amount in A1:C1 which is cell B1 for the amount of 850. So basically, I want the cells to change to the lowest amount in the range. Thanks for your help. Hope you understand what I want to happen. Art. "Rick Rothstein" wrote: I'm not sure how being able to do that meshes with your earlier posted criteria... can you clarify? Here is my problem in understanding what you want... you said you wanted a lower number put in a cell to be copied across to the Column H and you said "if I have in A1 9, in B1 8, and in c1 I enter 10, it should still stay 8 since there is a lower amount in B1", but now you say you want "if I have in Cell A1 900 in Cell B1 850 and in cell C1 600 and I want to change C1 to 880"... I just don't understand the rules that are at work here... when can something be changed and when can't it? -- Rick (MVP - Excel) "art" wrote in message ... Great. Thanks so much for your help. (I guess I can't change a number E.G. in cell C1 from lower to a higher number if in cell B1 has a lower number than that. So if I have in Cell A1 900 in Cell B1 850 and in cell C1 600 and I want to change C1 to 880, it will not let me and change all the cell from C1 to the end H1 to "850". So in essence if there is a lower amount on the left, it should enter that amount but still let me change any cell to a higher one. Don't know if its possible.) However, Thanks again for this code. Art. "Rick Rothstein" wrote: Sorry, I had misread your requirements. Does this do what you want? Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Count = 1 And Not Intersect(Target, _ Range("A1:H10")) Is Nothing Then Range(Target, Cells(Target.Row, "H")).Value = _ WorksheetFunction.Min(Range("A1", Target)) End If Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "art" wrote in message ... Its not bad, however, i want to change it, that if I have in A1 9, in B1 8, and in c1 I enter 10, it should still stay 8 since there is a lower amount in B1. Thanks so much. "Rick Rothstein" wrote: Give this macro a try... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("A1:H10")) Is Nothing Then Range(Target, Cells(Target.Row, "H")).Value = Target.Value End If Application.EnableEvents = True End Sub To install the macro, right click the tab at the bottom of the worksheet where you want this functionality, select View Code from the popup menu that appears and copy/paste the above code into the code window that appears. That's it... now go back to your worksheet and enter your values into the range A1:H10. -- Rick (MVP - Excel) "art" wrote in message ... Hello: I would like to have a code that enters the smallest number on a row to the rest of the row. So lets say I use the range from A1 to H10. I want that for row 1 if it has in A1 the value 253 and in B1 the value 219 and in C1 the value 198, so the rest of that row (until H1) should have the value 198. And when I'll enter the value 153 in D1, then all the rest after D1 should update to 153 and so on. This should occur in each row in the range. I usually enter the amounts from left to right in the range. Thanks for any help. Art |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the smallest amount | Excel Discussion (Misc queries) | |||
ENTER NEGATIVE AMOUNT | Excel Discussion (Misc queries) | |||
when I enter an amount in the A column move the row to another she | Excel Worksheet Functions | |||
How do I see the amount in words (I will enter amount in numbers) | Excel Programming | |||
How do I enter a negative dollar amount? | Excel Discussion (Misc queries) |