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 select specified cells?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
krakatoa
General User
General User


Joined: 17 Nov 2011
Posts: 8

PostPosted: Thu Nov 17, 2011 7:32 am    Post subject: How to select specified cells? Reply with quote

Hello dear readers.

I have got a txt file, which has data output from some appliance in it. I need to chart these data. The problem is the data are dispersed throughout the file in a regular patern. Basicly I need to select from column B every other 5th cell. Then I would like to copy selected values to other column.
Back to top
View user's profile Send private message
rrashkin
General User
General User


Joined: 30 Aug 2011
Posts: 44
Location: 29N -127E

PostPosted: Thu Nov 17, 2011 10:09 am    Post subject: Reply with quote

1. is "every other 5th cell" the same as "every 10th cell"?
2. you say you have a text file but you talk about the data like it's already in a spreadsheet, so where exactly is the data?
3. if I were going to go down column A and select every 10th cell between row 1 and, say, row42, I'd use:
Code:
for i =0 to 42 step 10
   oSheet.getCellByPosition(0,i)
next

_________________
Bob Rashkin
Back to top
View user's profile Send private message Visit poster's website
krakatoa
General User
General User


Joined: 17 Nov 2011
Posts: 8

PostPosted: Thu Nov 17, 2011 10:41 am    Post subject: Reply with quote

1. yes
2. I load the txt file as csv file in calc.
3. Can you please write whole macro?

If i write:
Code:
Sub Main
for i =0 to 42 step 10
   oSheet.getCellByPosition(0,i)
next
End Sub


It gives me: "BASIC runtime error. Object variable not set."
But I dont know how to set variable.
Back to top
View user's profile Send private message
rrashkin
General User
General User


Joined: 30 Aug 2011
Posts: 44
Location: 29N -127E

PostPosted: Thu Nov 17, 2011 10:59 am    Post subject: Reply with quote

Quote:
Basicly I need to select from column B every other 5th cell. Then I would like to copy selected values to other column.

So, every 10th cell in ColumnB, take the value and do what?
_________________
Bob Rashkin
Back to top
View user's profile Send private message Visit poster's website
krakatoa
General User
General User


Joined: 17 Nov 2011
Posts: 8

PostPosted: Thu Nov 17, 2011 11:20 am    Post subject: Reply with quote

Than I'd like make a column vector from these values. That means to put these values in column.

e.g. I want to select cells A1, A4, A7 (every 3rd cell) from:
A1 100
A2 15
A3 16
A4 20
A5 30
A6 40
A7 80
A8 45
A9 78

and put them in somwhere else e.g.:
B1 100
B2 20
B3 80

In R it is called array sliceing but its quite tedious and confusing to make chart in R from such data.
Back to top
View user's profile Send private message
rrashkin
General User
General User


Joined: 30 Aug 2011
Posts: 44
Location: 29N -127E

PostPosted: Thu Nov 17, 2011 12:04 pm    Post subject: Reply with quote

Code:
Sub Main
        oDoc=ThisComponent
        document=oDoc.CurrentController.Frame
        oSheet=oDoc.Sheets(0)
        n=0
        for i =0 to 9 step 3
          a= oSheet.getCellByPosition(0,i).value
          oSheet.getCellByPosition(1,n).value=a
          n=n+1
        next
End Sub

_________________
Bob Rashkin
Back to top
View user's profile Send private message Visit poster's website
gerard24
OOo Enthusiast
OOo Enthusiast


Joined: 08 Jul 2011
Posts: 100
Location: France

PostPosted: Thu Nov 17, 2011 12:10 pm    Post subject: Reply with quote

If you want every 3 rows beginning in row 1 :

Code:
=OFFSET($A$1;3*(ROW(A1)-1);0)

fill down the formula.
_________________
LibreOffice 3.5.0 on Windows Vista
Back to top
View user's profile Send private message
krakatoa
General User
General User


Joined: 17 Nov 2011
Posts: 8

PostPosted: Thu Nov 17, 2011 12:23 pm    Post subject: Reply with quote

Thank you Bob. But I don't know how to use it. I run the macro but it does nothing. If you dont't have patience with me please tell me where to look for macro creating guide for beginners which is comprehensive.
Back to top
View user's profile Send private message
krakatoa
General User
General User


Joined: 17 Nov 2011
Posts: 8

PostPosted: Thu Nov 17, 2011 12:39 pm    Post subject: Reply with quote

gerard24 wrote:
If you want every 3 rows beginning in row 1 :

Code:
=OFFSET($A$1;3*(ROW(A1)-1);0)

fill down the formula.


Nice that does the trick. But still to have macro which constructs new new calc document from the original data would be nicer.
Back to top
View user's profile Send private message
rrashkin
General User
General User


Joined: 30 Aug 2011
Posts: 44
Location: 29N -127E

PostPosted: Thu Nov 17, 2011 12:41 pm    Post subject: Reply with quote

I created a blank spreadsheet.
In Column A I entered
100
15
16
20
30
40
80
45
78

Then I went to the Macros:
alt+F11, then <this spreadsheet name>, then "standard", then "new"
In the new module I inserted the code so it looked like this:
Code:
Sub Main
        oDoc=ThisComponent
        document=oDoc.CurrentController.Frame
        oSheet=oDoc.Sheets(0)
        n=0
        for i =0 to 9 step 3
          a= oSheet.getCellByPosition(0,i).value
          oSheet.getCellByPosition(1,n).value=a
          n=n+1
        next
End Sub


Then back on the spreadsheet, I ran the macro by Tools>Macros>Run Macro... then navigated to the macro I just created.
The spreadsheet now looks like:

Code:
100   100   
15   20   
16   80   
20   0   
30      
40      
80      
45      
78

_________________
Bob Rashkin
Back to top
View user's profile Send private message Visit poster's website
krakatoa
General User
General User


Joined: 17 Nov 2011
Posts: 8

PostPosted: Thu Nov 17, 2011 12:50 pm    Post subject: Reply with quote

Yes it works.
Bob please how do I change column from which the macro takes values and column (place) where the macro puts selected data?
Back to top
View user's profile Send private message
rrashkin
General User
General User


Joined: 30 Aug 2011
Posts: 44
Location: 29N -127E

PostPosted: Thu Nov 17, 2011 12:53 pm    Post subject: Reply with quote

in this: a= oSheet.getCellByPosition(0,i).value
"0" is the column (A) where the number is found.

In this: oSheet.getCellByPosition(1,n).value=a
"1" is the column (B) where the number is placed.
_________________
Bob Rashkin
Back to top
View user's profile Send private message Visit poster's website
krakatoa
General User
General User


Joined: 17 Nov 2011
Posts: 8

PostPosted: Thu Nov 17, 2011 1:11 pm    Post subject: Reply with quote

Wow. Powerfull. My problem is partialy solved. It does what I needed.

Also I would like to put selected data in new document or append to old one.

How did you learned macro programing?
Back to top
View user's profile Send private message
rrashkin
General User
General User


Joined: 30 Aug 2011
Posts: 44
Location: 29N -127E

PostPosted: Fri Nov 18, 2011 6:24 am    Post subject: Reply with quote

BASIC is BASIC (ABASIC>GWBasic>VBA>OOBasic). I'm still struggling with the OOo API (star, uno, ... they're all just magical incantations to me).
_________________
Bob Rashkin
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
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