Random number Macro

[Mike Rogers]

XL2K

Is it possible to use a macro to place a random number in the active cell,
and not re-calc? I am trying to place a 4 diget ID number in a cell and need
it to not re-calc. I would like it to be random as opposed to assigned. Is
there a better way?

Mike Rogers

[Sloth]

Sub Macro1()
ActiveCell.FormulaR1C1 = "=TEXT(ROUND(10000*RAND(),0),""0000"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Sub


"Mike Rogers" wrote:

> XL2K
>
> Is it possible to use a macro to place a random number in the active cell,
> and not re-calc? I am trying to place a 4 diget ID number in a cell and need
> it to not re-calc. I would like it to be random as opposed to assigned. Is
> there a better way?
>
> Mike Rogers

[Mike Rogers]

Sloth

Thank you for the speedy response!!! One thing that is really important that
I did not mention in my original post. (opps) The active cell to place this
random number is in the range of E17:E50. Would this macro be placed in the
sheet Module? (Select tab, view code, setect "DataInput" sheet and paste)

Mike Rogers

"Sloth" wrote:

> Sub Macro1()
> ActiveCell.FormulaR1C1 = "=TEXT(ROUND(10000*RAND(),0),""0000"")"
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues
> Application.CutCopyMode = False
> End Sub
>
>
> "Mike Rogers" wrote:
>
> > XL2K
> >
> > Is it possible to use a macro to place a random number in the active cell,
> > and not re-calc? I am trying to place a 4 diget ID number in a cell and need
> > it to not re-calc. I would like it to be random as opposed to assigned. Is
> > there a better way?
> >
> > Mike Rogers

[Sloth]

for a range of cells use this. Select the range you want to insert the
numbers in and run the macro.

Sub Macro1()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.FormulaR1C1 = "=TEXT(ROUND(10000*RAND(),0),""0000"")"
Next myCell
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Sub

To your other question about where to place it. It goes in a module. Right
click the sheet tab and select "View code". Right click on ThisWorkbook and
goto
Insert->Module. Paste the code there.

Forgive me, but I don't know what "DataInput" is. My knowledge of Macros
and VBA is extremely limited.

"Mike Rogers" wrote:

> Sloth
>
> Thank you for the speedy response!!! One thing that is really important that
> I did not mention in my original post. (opps) The active cell to place this
> random number is in the range of E17:E50. Would this macro be placed in the
> sheet Module? (Select tab, view code, setect "DataInput" sheet and paste)
>
> Mike Rogers
>
> "Sloth" wrote:
>
> > Sub Macro1()
> > ActiveCell.FormulaR1C1 = "=TEXT(ROUND(10000*RAND(),0),""0000"")"
> > Selection.Copy
> > Selection.PasteSpecial Paste:=xlValues
> > Application.CutCopyMode = False
> > End Sub
> >
> >
> > "Mike Rogers" wrote:
> >
> > > XL2K
> > >
> > > Is it possible to use a macro to place a random number in the active cell,
> > > and not re-calc? I am trying to place a 4 diget ID number in a cell and need
> > > it to not re-calc. I would like it to be random as opposed to assigned. Is
> > > there a better way?
> > >
> > > Mike Rogers

[Mike Rogers]

Sloth

Thanks for the code... works like you knew it would...
Ohhh yea "DataInput" is the name of my worksheet I thought the code might go
in.

Got it working and thanks again for the help

Mike Rogers

"Sloth" wrote:

> for a range of cells use this. Select the range you want to insert the
> numbers in and run the macro.
>
> Sub Macro1()
> Dim myCell As Range
> For Each myCell In Selection.Cells
> myCell.FormulaR1C1 = "=TEXT(ROUND(10000*RAND(),0),""0000"")"
> Next myCell
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues
> Application.CutCopyMode = False
> End Sub
>
> To your other question about where to place it. It goes in a module. Right
> click the sheet tab and select "View code". Right click on ThisWorkbook and
> goto
> Insert->Module. Paste the code there.
>
> Forgive me, but I don't know what "DataInput" is. My knowledge of Macros
> and VBA is extremely limited.
>
> "Mike Rogers" wrote:
>
> > Sloth
> >
> > Thank you for the speedy response!!! One thing that is really important that
> > I did not mention in my original post. (opps) The active cell to place this
> > random number is in the range of E17:E50. Would this macro be placed in the
> > sheet Module? (Select tab, view code, setect "DataInput" sheet and paste)
> >
> > Mike Rogers
> >
> > "Sloth" wrote:
> >
> > > Sub Macro1()
> > > ActiveCell.FormulaR1C1 = "=TEXT(ROUND(10000*RAND(),0),""0000"")"
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlValues
> > > Application.CutCopyMode = False
> > > End Sub
> > >
> > >
> > > "Mike Rogers" wrote:
> > >
> > > > XL2K
> > > >
> > > > Is it possible to use a macro to place a random number in the active cell,
> > > > and not re-calc? I am trying to place a 4 diget ID number in a cell and need
> > > > it to not re-calc. I would like it to be random as opposed to assigned. Is
> > > > there a better way?
> > > >
> > > > Mike Rogers