change all below or equal to an input number

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

Hi

I need a small routine which will change all below or equal to an input
number to a second input number.

So for example ;

Message box 1 : Select column to work on

Message box 2 : Select all cells equal to or below input number

Message box 3 : Change selected cells to input number.


Therefore choosing column D , all below or equal to 3.50 , changing to
5.00 :

3.20
6.50
9.40
2.80
1.90
3.50
15.60

would become

5.00
6.50
9.40
5.00
5.00
5.00
15.60


Grateful for any help.


Are you aware that we also buy CDs, Vinyl and DVDs? Send your lists of
unwanted items to (e-mail address removed) and we’ll quote you a price…


You can browse and buy direct from my full list of items at these addresses :

http://s1.amazon.co.uk/exec/varzea/ts/my-zshop/SPJK3X6KOJZR6/026-3393902-9
050050

or:

http://www.CDandVinyl.co.uk

or :

http://www.netsoundsmusic.com/cdandvinyl

or:

http://chayes.musicstack.com



To DOWNLOAD the full catalogue click here :


http://www.chayes.demon.co.uk/CDandVinyl_full_catalogue.exe



Best Wishes ,

Colin Hayes.



TEL / FAX : (UK) (0)208 804 9181
 
Hi Colin,

Am Fri, 11 Jul 2014 23:42:50 +0100 schrieb Colin Hayes:
Message box 1 : Select column to work on

Message box 2 : Select all cells equal to or below input number

Message box 3 : Change selected cells to input number.
Click to expand...

I put all informations in 1 InputBox. Enter in the InputBox e.g.
D;3.5;5

Sub Test()
Dim LRow As Long
Dim rngC As Range
Dim strCond As String
Dim arrCond As Variant

With ActiveSheet
strCond = Application.InputBox("Enter the column to work on, " _
& "the max. number and the input number semicolon-separated", _
"Data Input", Type:=2)

If strCond <> "" And strCond <> "False" Then
arrCond = Split(strCond, ";")
Else
Exit Sub
End If

LRow = .Cells(Rows.Count, Asc(UCase(arrCond(0))) - 64).End(xlUp).Row
For Each rngC In .Range(.Cells(1, arrCond(0)), .Cells(LRow,
arrCond(0)))
If rngC <= CDbl(arrCond(1)) Then
rngC = CDbl(arrCond(2))
End If
Next
End With
End Sub


Regards
Claus B.
 
Claus Busch said:
Hi Colin,

Am Fri, 11 Jul 2014 23:42:50 +0100 schrieb Colin Hayes:
Click to expand...


Hi Claus

Many thanks - all fine.

In some circumstances , the column would need to change to be
NumberFormat = "0.00" , can the chosen column be made to be in this
format?

Also , it would help if the changed cells were made red font so I can
see the changed cells.

My thanks again , Claus. I'm grateful for your help.



Best Wishes
 
Hi Colin,

Am Sat, 12 Jul 2014 14:31:33 +0100 schrieb Colin Hayes:
In some circumstances , the column would need to change to be
NumberFormat = "0.00" , can the chosen column be made to be in this
format?

Also , it would help if the changed cells were made red font so I can
see the changed cells.
Click to expand...

try:

Sub Test()
Dim LRow As Long
Dim rngC As Range
Dim strCond As String
Dim arrCond As Variant

With ActiveSheet
strCond = Application.InputBox("Enter the column to work on, " _
& "the max. number and the input number semicolon-separated", _
"Data Input", Type:=2)

If strCond = "" Or strCond = "False" Then Exit Sub

arrCond = Split(strCond, ";")

LRow = .Cells(Rows.Count, Asc(UCase(arrCond(0))) - 64).End(xlUp).Row
For Each rngC In .Range(.Cells(1, arrCond(0)), _
.Cells(LRow, arrCond(0)))
If rngC <= CDbl(arrCond(1)) Then
rngC = CDbl(arrCond(2))
rngC.Font.Color = vbRed
End If
Next
.Range(.Cells(1, arrCond(0)), .Cells(LRow, arrCond(0))) _
.NumberFormat = "0.00"
End With
End Sub


Regards
Claus B.
 
Claus Busch said:
try:

Sub Test()
Dim LRow As Long
Dim rngC As Range
Dim strCond As String
Dim arrCond As Variant

With ActiveSheet
strCond = Application.InputBox("Enter the column to work on, " _
& "the max. number and the input number semicolon-separated", _
"Data Input", Type:=2)

If strCond = "" Or strCond = "False" Then Exit Sub

arrCond = Split(strCond, ";")

LRow = .Cells(Rows.Count, Asc(UCase(arrCond(0))) - 64).End(xlUp).Row
For Each rngC In .Range(.Cells(1, arrCond(0)), _
.Cells(LRow, arrCond(0)))
If rngC <= CDbl(arrCond(1)) Then
rngC = CDbl(arrCond(2))
rngC.Font.Color = vbRed
End If
Next
.Range(.Cells(1, arrCond(0)), .Cells(LRow, arrCond(0))) _
.NumberFormat = "0.00"
End With
End Sub
Click to expand...

Hi Claus

Yes - this is perfect.

Many thanks again.



Best Wishes


Colin
 
Back
Top