OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to fill multiple cells w/ random numbers with one macro?
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Mon May 28, 2012 11:22 am    Post subject: How to fill multiple cells w/ random numbers with one macro? Reply with quote

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
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Mon May 28, 2012 8:44 pm    Post subject: Reply with quote

Hi
Put your Formula in B2 fill down to B7...

For Recalculation hit STRG+shift+F9

Karo
Back to top
View user's profile Send private message
patel
Power User
Power User


Joined: 14 Apr 2012
Posts: 54
Location: Italy

PostPosted: Mon May 28, 2012 9:28 pm    Post subject: Reply with quote

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
View user's profile Send private message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Tue May 29, 2012 7:53 am    Post subject: Reply with quote

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
View user's profile Send private message
patel
Power User
Power User


Joined: 14 Apr 2012
Posts: 54
Location: Italy

PostPosted: Tue May 29, 2012 8:45 am    Post subject: Reply with quote

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
View user's profile Send private message
DiGro
Super User
Super User


Joined: 02 Jun 2004
Posts: 1415
Location: Hoorn NH, The Netherlands

PostPosted: Tue May 29, 2012 9:02 am    Post subject: Reply with quote

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 AOO 4.0.1 NL (Dutch)
Back to top
View user's profile Send private message
patel
Power User
Power User


Joined: 14 Apr 2012
Posts: 54
Location: Italy

PostPosted: Tue May 29, 2012 9:43 pm    Post subject: Reply with quote

yes, you are right, sorry for this little mistake Very Happy
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Wed May 30, 2012 10:43 am    Post subject: Reply with quote

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
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Thu May 31, 2012 1:17 am    Post subject: Reply with quote

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..
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Fri Jun 01, 2012 3:43 pm    Post subject: Reply with quote

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
View user's profile Send private message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Fri Jun 01, 2012 3:46 pm    Post subject: Reply with quote

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
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Sat Jun 02, 2012 1:02 am    Post subject: Reply with quote

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:
Code:
int(rnd()*10) + 9

to
Code:
int(rnd()*11) + 8

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)

_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Sat Jun 02, 2012 11:35 am    Post subject: Reply with quote

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
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Sun Jun 03, 2012 3:20 am    Post subject: Reply with quote

Code:
osheet = ThisComponent.Sheets(1)
uCell = osheet.getCellRangebyName("B3")

_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Sun Jun 03, 2012 11:10 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group