Formula in macro help
 

Hi Folks!

I use this formula extensively for testing:

=ROUND(RAND()*100,0)

That simply generates random 2 digit integers.

What I would like is a macro that will insert that formula into the selected
range then do the equivalent of CopyPaste SpecialValues.

It doesn't necessarily have to "insert that formula" just so the macro
generates random 2 digit ints in the selected range.

I use this so often it makes sense to have a macro that I can attach to a
toolbar button.

Thanks!

Biff

Formula in macro help
 

One way, if you have the range selected:

Sub Test()
With Selection
.Formula = "=ROUND(RAND()*100,0)"
.Copy
.PasteSpecial xlValues
End With
'Application.CutCopyMode = False
End Sub

Or you could just use Range("A1:A2") or equivalent insead of Selection. This
would be better as there would be no need then to select the range.

Regards,

Vasant

"Biff" wrote in message
...
Hi Folks!

I use this formula extensively for testing:

=ROUND(RAND()*100,0)

That simply generates random 2 digit integers.

What I would like is a macro that will insert that formula into the
selected range then do the equivalent of CopyPaste SpecialValues.

It doesn't necessarily have to "insert that formula" just so the macro
generates random 2 digit ints in the selected range.

I use this so often it makes sense to have a macro that I can attach to a
toolbar button.

Thanks!

Biff

Formula in macro help
 

Biff,

Sub test()
Selection.Formula = "=ROUND(RAND()*100,0)"
Selection.Value = Selection.Value
End Sub

hth,

Doug


"Biff" wrote in message
...
Hi Folks!

I use this formula extensively for testing:

=ROUND(RAND()*100,0)

That simply generates random 2 digit integers.

What I would like is a macro that will insert that formula into the
selected range then do the equivalent of CopyPaste SpecialValues.

It doesn't necessarily have to "insert that formula" just so the macro
generates random 2 digit ints in the selected range.

I use this so often it makes sense to have a macro that I can attach to a
toolbar button.

Thanks!

Biff

Formula in macro help
 

Thanks Vasant. Perfect!

Biff

"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
One way, if you have the range selected:

Sub Test()
With Selection
.Formula = "=ROUND(RAND()*100,0)"
.Copy
.PasteSpecial xlValues
End With
'Application.CutCopyMode = False
End Sub

Or you could just use Range("A1:A2") or equivalent insead of Selection.
This would be better as there would be no need then to select the range.

Regards,

Vasant

"Biff" wrote in message
...
Hi Folks!

I use this formula extensively for testing:

=ROUND(RAND()*100,0)

That simply generates random 2 digit integers.

What I would like is a macro that will insert that formula into the
selected range then do the equivalent of CopyPaste SpecialValues.

It doesn't necessarily have to "insert that formula" just so the macro
generates random 2 digit ints in the selected range.

I use this so often it makes sense to have a macro that I can attach to a
toolbar button.

Thanks!

Biff

Formula in macro help
 

Doug's approach is more efficient, actually.

--

Vasant


"Biff" wrote in message
...
Thanks Vasant. Perfect!

Biff

"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
One way, if you have the range selected:

Sub Test()
With Selection
.Formula = "=ROUND(RAND()*100,0)"
.Copy
.PasteSpecial xlValues
End With
'Application.CutCopyMode = False
End Sub

Or you could just use Range("A1:A2") or equivalent insead of Selection.
This would be better as there would be no need then to select the range.

Regards,

Vasant

"Biff" wrote in message
...
Hi Folks!

I use this formula extensively for testing:

=ROUND(RAND()*100,0)

That simply generates random 2 digit integers.

What I would like is a macro that will insert that formula into the
selected range then do the equivalent of CopyPaste SpecialValues.

It doesn't necessarily have to "insert that formula" just so the macro
generates random 2 digit ints in the selected range.

I use this so often it makes sense to have a macro that I can attach to
a toolbar button.

Thanks!

Biff

Formula in macro help
 

Thanks Doug.

Man, that seemed too easy! I gotta get up to speed with VBA one of these
days.

Biff

"Doug Glancy" wrote in message
...
Biff,

Sub test()
Selection.Formula = "=ROUND(RAND()*100,0)"
Selection.Value = Selection.Value
End Sub

hth,

Doug


"Biff" wrote in message
...
Hi Folks!

I use this formula extensively for testing:

=ROUND(RAND()*100,0)

That simply generates random 2 digit integers.

What I would like is a macro that will insert that formula into the
selected range then do the equivalent of CopyPaste SpecialValues.

It doesn't necessarily have to "insert that formula" just so the macro
generates random 2 digit ints in the selected range.

I use this so often it makes sense to have a macro that I can attach to a
toolbar button.

Thanks!

Biff

Formula in macro help
 

Ok, appreciate the effort!

Biff

"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
Doug's approach is more efficient, actually.

--

Vasant


"Biff" wrote in message
...
Thanks Vasant. Perfect!

Biff

"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
One way, if you have the range selected:

Sub Test()
With Selection
.Formula = "=ROUND(RAND()*100,0)"
.Copy
.PasteSpecial xlValues
End With
'Application.CutCopyMode = False
End Sub

Or you could just use Range("A1:A2") or equivalent insead of Selection.
This would be better as there would be no need then to select the range.

Regards,

Vasant

"Biff" wrote in message
...
Hi Folks!

I use this formula extensively for testing:

=ROUND(RAND()*100,0)

That simply generates random 2 digit integers.

What I would like is a macro that will insert that formula into the
selected range then do the equivalent of CopyPaste SpecialValues.

It doesn't necessarily have to "insert that formula" just so the macro
generates random 2 digit ints in the selected range.

I use this so often it makes sense to have a macro that I can attach to
a toolbar button.

Thanks!

Biff

Formula in macro help
 

You're welcome.

Doug

"Biff" wrote in message
...
Thanks Doug.

Man, that seemed too easy! I gotta get up to speed with VBA one of these
days.

Biff

"Doug Glancy" wrote in message
...
Biff,

Sub test()
Selection.Formula = "=ROUND(RAND()*100,0)"
Selection.Value = Selection.Value
End Sub

hth,

Doug


"Biff" wrote in message
...
Hi Folks!

I use this formula extensively for testing:

=ROUND(RAND()*100,0)

That simply generates random 2 digit integers.

What I would like is a macro that will insert that formula into the
selected range then do the equivalent of CopyPaste SpecialValues.

It doesn't necessarily have to "insert that formula" just so the macro
generates random 2 digit ints in the selected range.

I use this so often it makes sense to have a macro that I can attach to
a toolbar button.

Thanks!

Biff

Formula in macro help
 

Just another option:

Sub Demo()
ActiveCell = [ROUND(RAND()*100,0)]
End Sub

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"Biff" wrote in message
...
Thanks Doug.

Man, that seemed too easy! I gotta get up to speed with VBA one of these
days.

Biff

"Doug Glancy" wrote in message
...
Biff,

Sub test()
Selection.Formula = "=ROUND(RAND()*100,0)"
Selection.Value = Selection.Value
End Sub

hth,

Doug


"Biff" wrote in message
...
Hi Folks!

I use this formula extensively for testing:

=ROUND(RAND()*100,0)

That simply generates random 2 digit integers.

What I would like is a macro that will insert that formula into the
selected range then do the equivalent of CopyPaste SpecialValues.

It doesn't necessarily have to "insert that formula" just so the macro
generates random 2 digit ints in the selected range.

I use this so often it makes sense to have a macro that I can attach to
a toolbar button.

Thanks!

Biff

Formula in macro help
 

Oops. That won't work. My mistake. You said a range. Didn't notice till
after hitting send. Sorry.

--
Dana


"Dana DeLouis" wrote in message
...
Just another option:

Sub Demo()
ActiveCell = [ROUND(RAND()*100,0)]
End Sub

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"Biff" wrote in message
...
Thanks Doug.

Man, that seemed too easy! I gotta get up to speed with VBA one of these
days.

Biff

"Doug Glancy" wrote in message
...
Biff,

Sub test()
Selection.Formula = "=ROUND(RAND()*100,0)"
Selection.Value = Selection.Value
End Sub

hth,

Doug


"Biff" wrote in message
...
Hi Folks!

I use this formula extensively for testing:

=ROUND(RAND()*100,0)

That simply generates random 2 digit integers.

What I would like is a macro that will insert that formula into the
selected range then do the equivalent of CopyPaste SpecialValues.

It doesn't necessarily have to "insert that formula" just so the macro
generates random 2 digit ints in the selected range.

I use this so often it makes sense to have a macro that I can attach to
a toolbar button.

Thanks!

Biff

Formula in macro help
 

Dana,

I tried something like that- are the brackets the same as Evaluate?
Anyways, since Biff wants to fill a range, I don't think thatworks - at
least when I change Activecell to Selection I get the same "random" number
in each cell.

Doug

"Dana DeLouis" wrote in message
...
Just another option:

Sub Demo()
ActiveCell = [ROUND(RAND()*100,0)]
End Sub

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"Biff" wrote in message
...
Thanks Doug.

Man, that seemed too easy! I gotta get up to speed with VBA one of these
days.

Biff

"Doug Glancy" wrote in message
...
Biff,

Sub test()
Selection.Formula = "=ROUND(RAND()*100,0)"
Selection.Value = Selection.Value
End Sub

hth,

Doug


"Biff" wrote in message
...
Hi Folks!

I use this formula extensively for testing:

=ROUND(RAND()*100,0)

That simply generates random 2 digit integers.

What I would like is a macro that will insert that formula into the
selected range then do the equivalent of CopyPaste SpecialValues.

It doesn't necessarily have to "insert that formula" just so the macro
generates random 2 digit ints in the selected range.

I use this so often it makes sense to have a macro that I can attach to
a toolbar button.

Thanks!

Biff