| View previous topic :: View next topic |
| Author |
Message |
Sithlyone
Power User
Joined: 13 Jan 2012
Posts: 98
|
 Posted: Mon May 28, 2012 11:22 am Post subject: How to fill multiple cells w/ random numbers with one macro? |
 |
|
Hello, I am using Randbetween to give me a random number set between 9-18. I have this macro set up to randomly choose this number every time I click a button. Is there a way to have multiple cells generate a random number between those numbers?
I would like to have this one macro change 6 cells at a single click of a button. I'm looking to have B2-B7 populated with random numbers each time I click the button.
Does anyone know how I would change this macro to accomplish this? Thanks so much for your help.
| Code: | REM ***** BASIC *****
Sub UpdateCell
dim uCell as object
oSheet = ThisComponent.Sheets(0)
uCell = oSheet.getCellRangebyName("B2")
UpdateZelle (uCell)
End Sub
Sub UpdateZelle ( uCell as object )
' ----------------------------------------------------------------------
' Macro by gazelle
' V1 started: 14.Jan.2009 finnished: 15.Jan.2009
' read formula in target cell and update the displayed result
' ----------------------------------------------------------------------
dim oCell as object, oSheet as object ' current active cell
' uCell as object ' cell to be updated
oSheet = ThisComponent.CurrentController.ActiveSheet
oCell = ThisComponent.getCurrentSelection
ThisComponent.CurrentController.select(uCell) ' activate cell concerned
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
uCell.Formula = "=randbetween(9;18)"
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "StringName"
args2(0).Value = uCell.Formula ' get formula to be updated
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
' memo:
' uCell.Formula = „string as formla“ does not update the displayed result
ThisComponent.CurrentController.select(oCell) ' back to previously active cell
end sub |
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help! |
|
| Back to top |
|
 |
karolus
OOo Advocate
Joined: 22 Jun 2011
Posts: 208
|
| Posted: Mon May 28, 2012 8:44 pm Post subject: |
|
|
Hi
Put your Formula in B2 fill down to B7...
For Recalculation hit STRG+shift+F9
Karo |
|
| Back to top |
|
|
patel
Power User
Joined: 14 Apr 2012
Posts: 54
Location: Italy
|
| Posted: Mon May 28, 2012 9:28 pm Post subject: |
|
|
You can use this macro for recalculation
| Code: | sub Main
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:CalculateHard", "", 0, Array())
end sub |
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Last edited by patel on Tue May 29, 2012 9:41 pm; edited 1 time in total |
|
| Back to top |
|
|
Sithlyone
Power User
Joined: 13 Jan 2012
Posts: 98
|
| Posted: Tue May 29, 2012 7:53 am Post subject: |
|
|
Thanks so much for the reply's.
Patel, what do I put in the array area? I assisgned that to my button and when I click it, it brings up an error and highlights that sentence.
Do I need to put the cells that are effected? If so how do I write it? I tried a few different ways and kept getting errors.
Also, will this just recalculate those particular cells or the whole sheet?
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help! |
|
| Back to top |
|
|
patel
Power User
Joined: 14 Apr 2012
Posts: 54
Location: Italy
|
| Posted: Tue May 29, 2012 8:45 am Post subject: |
|
|
my macro recalculate the whole sheet, it's the same as Ctrl+Shift+F9
I got it with the macro recorder.
Array() is a dummy array
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
DiGro
Super User
Joined: 02 Jun 2004
Posts: 1210
Location: Hoorn NH, The Netherlands
|
| Posted: Tue May 29, 2012 9:02 am Post subject: |
|
|
You probably have to type a letter "d" before "ispatcher" in this line
| Code: | | ispatcher.executeDispatch(document, ".uno:CalculateHard", "", 0, Array()) |
_________________
DiGro
Windows 7 Home Premium and OOo 3.3 NL (Dutch) |
|
| Back to top |
|
|
patel
Power User
Joined: 14 Apr 2012
Posts: 54
Location: Italy
|
| Posted: Tue May 29, 2012 9:43 pm Post subject: |
|
|
yes, you are right, sorry for this little mistake 
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
Sithlyone
Power User
Joined: 13 Jan 2012
Posts: 98
|
| Posted: Wed May 30, 2012 10:43 am Post subject: |
|
|
Thanks, that did fix the error issue.
One other thing before I feel this is truly solved, is there a way to make a macro just recalculate the particular cells that you want it too?
I have another spreadsheet that has several "rollers" in them and they just change their particular cell. Couldn't I just place a few of those into this one macro and have it change just their assigned cells?
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help! |
|
| Back to top |
|
|
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Thu May 31, 2012 1:17 am Post subject: |
|
|
| Code: | Sub RandomB2B7
Dim B2_rnd, B3_rnd, B4_rnd, B5_rnd, B6_rnd, B7_rnd
xSheet = ThisComponent.Sheets(0)
B2_rnd = xSheet.getCellRangeByName("B2")
B3_rnd = xSheet.getCellRangeByName("B3")
B4_rnd = xSheet.getCellRangeByName("B4")
B5_rnd = xSheet.getCellRangeByName("B5")
B6_rnd = xSheet.getCellRangeByName("B6")
B7_rnd = xSheet.getCellRangeByName("B7")
B2_rnd.value = int(rnd()*10) + 9
B3_rnd.value = int(rnd()*10) + 9
B3_rnd.value = int(rnd()*10) + 9
B4_rnd.value = int(rnd()*10) + 9
B5_rnd.value = int(rnd()*10) + 9
B6_rnd.value = int(rnd()*10) + 9
End Sub |
There's probably a neater way with DO loops but since there's only six cells involved..
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
Sithlyone
Power User
Joined: 13 Jan 2012
Posts: 98
|
| Posted: Fri Jun 01, 2012 3:43 pm Post subject: |
|
|
Thanks so much RT! You come to my rescue again...
However I have noticed two things. It is suppose to be a range from 8-18. While I get many high numbers, including 18's I never get 8's.
second I get A LOT more high numbers than I do the lower numbers.
Low numbers range from 8-10 and high numbers range from 16-18, mid numbers range from 11-15. Is there a way to make the distribution more equal among the different ranges?
A non related question, but one I need answered non the less, thanks.
I also have another question: How do I reference a cell on Sheet2? When I assign a macro to a button it always puts the results cell on the first page. I would like to know how to write the reference so that it puts it on a different page within the same document. I have macros on the first page as well, but I want specific ones on different pages.
Edit: I just noticed that the "B7" doesn't change, is there a error in the code?
Again, thanks for the help.
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help!
Last edited by Sithlyone on Fri Jun 01, 2012 6:02 pm; edited 1 time in total |
|
| Back to top |
|
|
Sithlyone
Power User
Joined: 13 Jan 2012
Posts: 98
|
| Posted: Fri Jun 01, 2012 3:46 pm Post subject: |
|
|
I figured out why B7 didn't change. The code is missing for that particular line. I just added it and it works fine now. However I'm still getting the uneven rolls.
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help! |
|
| Back to top |
|
|
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Sat Jun 02, 2012 1:02 am Post subject: |
|
|
| Sithlyone wrote: | | However I have noticed two things. It is suppose to be a range from 8-18. While I get many high numbers, including 18's I never get 8's. |
I think you wrote 9-18 above. Change all:
to
| Sithlyone wrote: | second I get A LOT more high numbers than I do the lower numbers.
Low numbers range from 8-10 and high numbers range from 16-18, mid numbers range from 11-15. Is there a way to make the distribution more equal among the different ranges? |
Are you sure?
| Sithlyone wrote: | | I also have another question: How do I reference a cell on Sheet2? When I assign a macro to a button it always puts the results cell on the first page. I would like to know how to write the reference so that it puts it on a different page within the same document. I have macros on the first page as well, but I want specific ones on different pages. |
For Sheet2, change:
| Code: | | xSheet = ThisComponent.Sheets(0) |
to
| Code: | | xSheet = ThisComponent.Sheets(1) |
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
Sithlyone
Power User
Joined: 13 Jan 2012
Posts: 98
|
| Posted: Sat Jun 02, 2012 11:35 am Post subject: |
|
|
Thanks so much, that change worked. Once I did that I noticed that I both started to get 8's and it became much more evenly placed among the ranges.
On the sheet reference question I couldn't figure out what I was suppose to do with your code. Like I said this is for a different macro and has nothing to do with this one. Here is the line of code that references the sheet and I can't figure out how to label it so that it places it on the second page.
| Code: | | uCell = osheet.getCellRangebyName("=SHEET2.b3") |
B3 is the cell I want it on, on sheet 2.
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help! |
|
| Back to top |
|
|
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Sun Jun 03, 2012 3:20 am Post subject: |
|
|
| Code: | osheet = ThisComponent.Sheets(1)
uCell = osheet.getCellRangebyName("B3") |
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
Sithlyone
Power User
Joined: 13 Jan 2012
Posts: 98
|
| Posted: Sun Jun 03, 2012 11:10 am Post subject: |
|
|
Alright, what if I wanted to change the name of Sheet 2 to something else? What do I change?
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help! |
|
| Back to top |
|
|
|
