Using a popup to affect a column of numbers

[Colin Hayes]

Hi All

I need to solve a problem , and would grateful if anyone can help.

I need to input a number via a popup. Then increase all numbers in a
column to the number entered in the popup. The numbers to apply the code
to would already be selected in the wb.

For example , if I entered the number 3 in the popup , all numbers equal
to or below 3 would be increased to 3. All those above would stay the
same.

Example :

Before :

1
2
3
4
5
6

After

3
3
3
4
5
6


Can someone help with some code?

Grateful or any assistance.

 

[Don Guillett]

something like
sub increasetothree()
newnum=inputbox("Enter new num"
for i= 2 to cells(rows.count, "a").end(xlup).row
if cells(i,"a")<newnum then cells(i,"a")=newnum
next i
end sub

 

[Colin Hayes]

something like
sub increasetothree()
newnum=inputbox("Enter new num"
for i= 2 to cells(rows.count, "a").end(xlup).row
if cells(i,"a")<newnum then cells(i,"a")=newnum
next i
end sub

HI Don

OK Thanks for that.

No joy I'm afraid. I ran it but it had no effect on the numbers I had
highlighted. It just needs to affect the selection.

I had these numbers highlighted in the column :

3.00
3.50
4.00
4.50
5.00
5.50
6.00
6.50
7.00
7.50
8.00
8.50
9.00
9.50


I ran the macro and entered the number 7 in the popup and clicked OK. No
effect I'm afraid. They just all stayed the same.

I was hoping for this outcome :

7.00
7.00
7.00
7.00
7.00
7.00
7.00
7.00
7.00
7.50
8.00
8.50
9.00
9.50

I did add a closed bracket after "Enter new num" in your code above.


Grateful if you could advise.

Thanks

 

[Don Guillett]

I tested the code and made a couple of changes. mc allows you to specify the
column. This could be done via input box. I used a dim statement for the
number. The first one does a column and the second does a selected range.

Sub increasetothree()
Dim mc As String
Dim i As Long
Dim newnum As Long
mc = "g"
newnum = InputBox("Enter new num")
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row
If Cells(i, mc) < newnum Then Cells(i, mc) = newnum
Next i
End Sub

Use this
Sub IncreaseSelectinToNumberDesired()
Dim newnum As Long
Dim c As Range
newnum = InputBox("Enter new num")
For Each c In Selection
If c < newnum Then c = newnum
Next c
End Sub

 

[Colin Hayes]

I tested the code and made a couple of changes. mc allows you to specify the
column. This could be done via input box. I used a dim statement for the
number. The first one does a column and the second does a selected range.

Sub increasetothree()
Dim mc As String
Dim i As Long
Dim newnum As Long
mc = "g"
newnum = InputBox("Enter new num")
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row
If Cells(i, mc) < newnum Then Cells(i, mc) = newnum
Next i
End Sub

Use this
Sub IncreaseSelectinToNumberDesired()
Dim newnum As Long
Dim c As Range
newnum = InputBox("Enter new num")
For Each c In Selection
If c < newnum Then c = newnum
Next c
End Sub

Hi Don

OK that's got it - it worked perfectly first time.

Thanks.

Best Wishes