| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using a popup to make percenatges on selected cells
Hi I'm hoping someone can help with some code. I'm selecting a group of cells containing numbers. I want via a popup to change the selected numbers to the percentage entered in the popup. For example - I select my cells and run the macro. The popup asks for a number and I enter '50'. All the selected numbers then change to 50 percent of their previous value. It would apply equally to a selected whole column of numbers. Grateful for any help. Best Wishes |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using a popup to make percenatges on selected cells
hi
Sub changenumber() Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each Cell In Selection Cell.Value = Cell * n Next Cell End Sub regards FSt1 "Colin Hayes" wrote: Hi I'm hoping someone can help with some code. I'm selecting a group of cells containing numbers. I want via a popup to change the selected numbers to the percentage entered in the popup. For example - I select my cells and run the macro. The popup asks for a number and I enter '50'. All the selected numbers then change to 50 percent of their previous value. It would apply equally to a selected whole column of numbers. Grateful for any help. Best Wishes . |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using a popup to make percenatges on selected cells
In article , FSt1
writes hi Sub changenumber() Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each Cell In Selection Cell.Value = Cell * n Next Cell End Sub regards FSt1 Hi FSt1 Thanks for your help I think the code is wrapped in the email. I tried it like this : Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each Cell In Selection Cell.Value = Cell * n Next Cell End Sub But it gives type mismatch errors in the Cell.Value = Cell * n line Cab you advise? Thanks "Colin Hayes" wrote: Hi I'm hoping someone can help with some code. I'm selecting a group of cells containing numbers. I want via a popup to change the selected numbers to the percentage entered in the popup. For example - I select my cells and run the macro. The popup asks for a number and I enter '50'. All the selected numbers then change to 50 percent of their previous value. It would apply equally to a selected whole column of numbers. Grateful for any help. Best Wishes . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using a popup to make percenatges on selected cells
hi
i tested it in 03 before posting it. tested on continuous and non continuous cells. it worked with no errors. i just retested with same results. i am sure you put it in a standard modual. i tried it a tad different. Sub changenumber() Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each C In Selection C.Value = C * n Next C End Sub i cannot understand why you would get mismatch errors because i am not. regards FSt1 "Colin Hayes" wrote: In article , FSt1 writes hi Sub changenumber() Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each Cell In Selection Cell.Value = Cell * n Next Cell End Sub regards FSt1 Hi FSt1 Thanks for your help I think the code is wrapped in the email. I tried it like this : Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each Cell In Selection Cell.Value = Cell * n Next Cell End Sub But it gives type mismatch errors in the Cell.Value = Cell * n line Cab you advise? Thanks "Colin Hayes" wrote: Hi I'm hoping someone can help with some code. I'm selecting a group of cells containing numbers. I want via a popup to change the selected numbers to the percentage entered in the popup. For example - I select my cells and run the macro. The popup asks for a number and I enter '50'. All the selected numbers then change to 50 percent of their previous value. It would apply equally to a selected whole column of numbers. Grateful for any help. Best Wishes . . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using a popup to make percenatges on selected cells
Hi FSt1 OK thanks for your help. The code works perfectly where a range of cells is selected in a column. The code gives errors where the whole column is selected. Somehow it would need to find the range between cell 2 and the last cell with content in the column. That would fix it. Thanks again - I'm grateful. Best Wishes In article , FSt1 writes hi i tested it in 03 before posting it. tested on continuous and non continuous cells. it worked with no errors. i just retested with same results. i am sure you put it in a standard modual. i tried it a tad different. Sub changenumber() Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each C In Selection C.Value = C * n Next C End Sub i cannot understand why you would get mismatch errors because i am not. regards FSt1 "Colin Hayes" wrote: In article , FSt1 writes hi Sub changenumber() Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each Cell In Selection Cell.Value = Cell * n Next Cell End Sub regards FSt1 Hi FSt1 Thanks for your help I think the code is wrapped in the email. I tried it like this : Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each Cell In Selection Cell.Value = Cell * n Next Cell End Sub But it gives type mismatch errors in the Cell.Value = Cell * n line Cab you advise? Thanks "Colin Hayes" wrote: Hi I'm hoping someone can help with some code. I'm selecting a group of cells containing numbers. I want via a popup to change the selected numbers to the percentage entered in the popup. For example - I select my cells and run the macro. The popup asks for a number and I enter '50'. All the selected numbers then change to 50 percent of their previous value. It would apply equally to a selected whole column of numbers. Grateful for any help. Best Wishes . . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using a popup to make percenatges on selected cells
Colin,
Modify FSt1's Sub : Sub changenumber() Dim n As Double, S As Range, C As Range n = InputBox("enter a number") n = n / 100 'changes to percent Set S = Intersect(Selection, ActiveSheet.UsedRange) For Each C In S C.Value = C * n Next C End Sub This supplement will restrict your transformation to the used range even if the whole column has been selected. -- Petr Bezucha "Colin Hayes" wrote: Hi FSt1 OK thanks for your help. The code works perfectly where a range of cells is selected in a column. The code gives errors where the whole column is selected. Somehow it would need to find the range between cell 2 and the last cell with content in the column. That would fix it. Thanks again - I'm grateful. Best Wishes In article , FSt1 writes hi i tested it in 03 before posting it. tested on continuous and non continuous cells. it worked with no errors. i just retested with same results. i am sure you put it in a standard modual. i tried it a tad different. Sub changenumber() Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each C In Selection C.Value = C * n Next C End Sub i cannot understand why you would get mismatch errors because i am not. regards FSt1 "Colin Hayes" wrote: In article , FSt1 writes hi Sub changenumber() Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each Cell In Selection Cell.Value = Cell * n Next Cell End Sub regards FSt1 Hi FSt1 Thanks for your help I think the code is wrapped in the email. I tried it like this : Dim n As Double n = InputBox("enter a number") n = n / 100 'changes to percent For Each Cell In Selection Cell.Value = Cell * n Next Cell End Sub But it gives type mismatch errors in the Cell.Value = Cell * n line Cab you advise? Thanks "Colin Hayes" wrote: Hi I'm hoping someone can help with some code. I'm selecting a group of cells containing numbers. I want via a popup to change the selected numbers to the percentage entered in the popup. For example - I select my cells and run the macro. The popup asks for a number and I enter '50'. All the selected numbers then change to 50 percent of their previous value. It would apply equally to a selected whole column of numbers. Grateful for any help. Best Wishes . . . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
using a popup to make percenatges on selected cells
In article ,
PBezucha writes Colin, Modify FSt1's Sub : Sub changenumber() Dim n As Double, S As Range, C As Range n = InputBox("enter a number") n = n / 100 'changes to percent Set S = Intersect(Selection, ActiveSheet.UsedRange) For Each C In S C.Value = C * n Next C End Sub This supplement will restrict your transformation to the used range even if the whole column has been selected. HI Petr OK Many thanks for that. It does still give a Type Mismatch error , because I have a header cell (which is text) at the top of my column. When I select the whole column , it gives this error. However , when I delete the header cell , it works perfectly. Is there a way for it to act from cell 2 down to the bottom of the column , and ignore the header cell? Or if you have it start at cell 2 , would it interfere with the working of the macro where a single smaller range of cells was selected rather than the whole column? Grateful for your help. Best Wishes |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple cells or columns are selected instead of selected cell or | Excel Discussion (Misc queries) | |||
| creating a popup answer for an average range of cells | Excel Worksheet Functions | |||
| Cells are selected but aren't displayed as selected | Excel Discussion (Misc queries) | |||
| How to make excel popup windows | Excel Discussion (Misc queries) | |||
| how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet. | Excel Worksheet Functions | |||
Linear Mode
