Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a popup to affect a column of numbers
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a popup to affect a column of numbers
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Colin Hayes" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a popup to affect a column of numbers
In article , Don Guillett
writes 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a popup to affect a column of numbers
In article , Don Guillett
writes 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automating change via popup to a column of numbers | Excel Worksheet Functions | |||
How does 1 value affect the average? | Excel Worksheet Functions | |||
How do business loans affect the p & l? | Excel Discussion (Misc queries) | |||
How do i make a change in one worksheet affect the others | Excel Worksheet Functions | |||
Does font affect indent spacing? | Excel Worksheet Functions |