| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using a formula in a macro | Excel Discussion (Misc queries) | |||
| Formula or Macro | Excel Discussion (Misc queries) | |||
| Need formula or Macro Help | Excel Worksheet Functions | |||
| Macro Formula for Max value | Excel Worksheet Functions | |||
| formula to a macro help PLEASE | Excel Discussion (Misc queries) | |||
Hybrid Mode
