I routinely need to divide large blocks of numbers by a specific value, most
often 1000. I have been placing 1000 in a cell, formating the cell, copy it,
then highlight the block of numbers, then edit, paste special, divide by.
How would I create a macro attached to key strokes that would allow me to
simply select the block of numbers and, apply the keystrokes say ctrl+shift+I
and have it divided by 1000 without having to do the copy, format first.
Any help would be appreciated
Thank you.
--
Dewayne

Sub DivideBy1000()
Dim cell As Range

For Each cell In Selection

cell.Value = cell.Value / 1000
Next cell
End Sub

--
__________________________________
HTH

Bob

"Dewayne" wrote in message
...
I routinely need to divide large blocks of numbers by a specific value,
most
often 1000. I have been placing 1000 in a cell, formating the cell, copy
it,
then highlight the block of numbers, then edit, paste special, divide by.
How would I create a macro attached to key strokes that would allow me to
simply select the block of numbers and, apply the keystrokes say
ctrl+shift+I
and have it divided by 1000 without having to do the copy, format first.
Any help would be appreciated
Thank you.
--
Dewayne

Here is the subroutine

Sub tryme()
For Each mycell In Selection
mycell.Value = mycell.Value / 1000
Next mycell
Range("A1").Select
End Sub

One of these sites will tell you how to place this in a module and assigning
a keystroke - come back if you need more help
David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html

BTW: why are you bothering with format before Copy | Paste Special ?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dewayne" wrote in message
...
I routinely need to divide large blocks of numbers by a specific value,
most
often 1000. I have been placing 1000 in a cell, formating the cell, copy
it,
then highlight the block of numbers, then edit, paste special, divide by.
How would I create a macro attached to key strokes that would allow me to
simply select the block of numbers and, apply the keystrokes say
ctrl+shift+I
and have it divided by 1000 without having to do the copy, format first.
Any help would be appreciated
Thank you.
--
Dewayne

Here is the macro:

Sub DivideandConquer()
For Each r In Selection
r.Value = r.Value / 1000
Next
End Sub

To assign the shortcut key:

ALT-F8 Options... and select the keys
--
Gary''s Student - gsnu200857


"Dewayne" wrote:

I routinely need to divide large blocks of numbers by a specific value, most
often 1000. I have been placing 1000 in a cell, formating the cell, copy it,
then highlight the block of numbers, then edit, paste special, divide by.
How would I create a macro attached to key strokes that would allow me to
simply select the block of numbers and, apply the keystrokes say ctrl+shift+I
and have it divided by 1000 without having to do the copy, format first.
Any help would be appreciated
Thank you.
--
Dewayne

Or you could use the same technique you used when you did it yourself:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim myValue As Double

myValue = CDbl(Application.InputBox(Prompt:="What's the number?", _
Default:=1000, Type:=1))

If myValue = 0 Then
Exit Sub
End If

Set myRng = Selection

With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
End With

myCell.Value = myValue

myCell.Copy
myRng.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlDivide, _
SkipBlanks:=False, _
Transpose:=False

myCell.ClearContents

End Sub


Dewayne wrote:

I routinely need to divide large blocks of numbers by a specific value, most
often 1000. I have been placing 1000 in a cell, formating the cell, copy it,
then highlight the block of numbers, then edit, paste special, divide by.
How would I create a macro attached to key strokes that would allow me to
simply select the block of numbers and, apply the keystrokes say ctrl+shift+I
and have it divided by 1000 without having to do the copy, format first.
Any help would be appreciated
Thank you.
--
Dewayne

--

Dave Peterson

Thanks to all of you for your input. It is much appreciated.
--
Dewayne


"Bob Phillips" wrote:

Sub DivideBy1000()
Dim cell As Range

For Each cell In Selection

cell.Value = cell.Value / 1000
Next cell
End Sub

--
__________________________________
HTH

Bob

"Dewayne" wrote in message
...
I routinely need to divide large blocks of numbers by a specific value,
most
often 1000. I have been placing 1000 in a cell, formating the cell, copy
it,
then highlight the block of numbers, then edit, paste special, divide by.
How would I create a macro attached to key strokes that would allow me to
simply select the block of numbers and, apply the keystrokes say
ctrl+shift+I
and have it divided by 1000 without having to do the copy, format first.
Any help would be appreciated
Thank you.
--
Dewayne

When I don't format the cell to the format of the target cells before the
copy | Paste Special, the target cells were reformated with the formate of
the copy from cell.
--
Dewayne


"Bernard Liengme" wrote:

Here is the subroutine

Sub tryme()
For Each mycell In Selection
mycell.Value = mycell.Value / 1000
Next mycell
Range("A1").Select
End Sub

One of these sites will tell you how to place this in a module and assigning
a keystroke - come back if you need more help
David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html

BTW: why are you bothering with format before Copy | Paste Special ?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dewayne" wrote in message
...
I routinely need to divide large blocks of numbers by a specific value,
most
often 1000. I have been placing 1000 in a cell, formating the cell, copy
it,
then highlight the block of numbers, then edit, paste special, divide by.
How would I create a macro attached to key strokes that would allow me to
simply select the block of numbers and, apply the keystrokes say
ctrl+shift+I
and have it divided by 1000 without having to do the copy, format first.
Any help would be appreciated
Thank you.
--
Dewayne