Formula in macro help

[Biff]

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 Copy>Paste Special>Values.

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

 

[Vasant Nanavati]

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

 

[Doug Glancy]

Biff,

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

hth,

Doug

 

[Biff]

Thanks Vasant. Perfect!

Biff

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

 

[Vasant Nanavati]

Doug's approach is more efficient, actually.

 

[Biff]

Thanks Doug.

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

Biff

 

[Biff]

Ok, appreciate the effort!

Biff

Doug's approach is more efficient, actually.

 

[Doug Glancy]

You're welcome.

Doug

Thanks Doug.

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

Biff

 

[Dana DeLouis]

Just another option:

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

HTH :>)

 

[Dana DeLouis]

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

--
Dana

Just another option:

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

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

Thanks Doug.

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

Biff

 

[Doug Glancy]

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

Just another option:

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

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

Thanks Doug.

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

Biff