Divide By

[Dewayne]

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.

 

[Bob Phillips]

Sub DivideBy1000()
Dim cell As Range

For Each cell In Selection

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

 

[Bernard Liengme]

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 ?

 

[Gary''s Student]

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

 

[Dave Peterson]

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]

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

 

[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.