Enter the smallest amount in row

[art]

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

 

[OssieMac]

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)

 

[art]

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.

 

[Jacob Skaria]

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

 

[Rick Rothstein]

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.

 

[art]

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?

 

[art]

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]

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

 

[art]

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]

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?

 

[art]

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.