using a popup to make percenatges on selected cells

[Colin Hayes]

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

 

[FSt1]

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]

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

 

[FSt1]

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]

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

 

[PBezucha]

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.

 

[Colin Hayes]

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