Getting Cell Value in a Macro

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Getting Cell Value in a Macro

Russell King-2
Hi,

I am trying to build a macro that will build a synopsis sheet from the
other sheets in a spreadsheet. The synopsis is over 1600 rows long, so
it is a bit tedious to do it by hand and since each sheet changes not
just the data, but the amount of data, the macro ends up putting in a
lot of references to cells that are empty in order to make sure it gets
it all. The macro then sorts the data which leaves over 600 rows of
zeros at the top. What I need is a function for my macro that will go
through the top of the sheet, leaving the header row alone, and delete
the rows that have a value (not content) of zero.

I have tried various code examples (ie, getCellByPosition), but it
doesn't work (unexpected syntax errors)

All I want to do is dump the value of a cell (ie $A$2) into a string
variable so that I can compare it. Why does it have to be so hard?

rk


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Getting Cell Value in a Macro

David Chapman
Russell,

My data input wizard includes some code that deletes rows and shifts
remaining data up.
You might be able to reuse some of it. In fact - there may be other
code in there that may be
useful to you.

http://www.openofficetips.com/blog/archives/2005/10/updated_data_en.html

Dave

On 11/29/05, Russell King <[hidden email]> wrote:

> Hi,
>
> I am trying to build a macro that will build a synopsis sheet from the
> other sheets in a spreadsheet. The synopsis is over 1600 rows long, so
> it is a bit tedious to do it by hand and since each sheet changes not
> just the data, but the amount of data, the macro ends up putting in a
> lot of references to cells that are empty in order to make sure it gets
> it all. The macro then sorts the data which leaves over 600 rows of
> zeros at the top. What I need is a function for my macro that will go
> through the top of the sheet, leaving the header row alone, and delete
> the rows that have a value (not content) of zero.
>
> I have tried various code examples (ie, getCellByPosition), but it
> doesn't work (unexpected syntax errors)
>
> All I want to do is dump the value of a cell (ie $A$2) into a string
> variable so that I can compare it. Why does it have to be so hard?
>
> rk
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


--
My OpenOffice Calc Website
http://www.openofficetips.com

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Getting Cell Value in a Macro

Andrew Pitonyak
In reply to this post by Russell King-2
Russell King wrote:

> Hi,
>
> I am trying to build a macro that will build a synopsis sheet from the
> other sheets in a spreadsheet. The synopsis is over 1600 rows long, so
> it is a bit tedious to do it by hand and since each sheet changes not
> just the data, but the amount of data, the macro ends up putting in a
> lot of references to cells that are empty in order to make sure it
> gets it all. The macro then sorts the data which leaves over 600 rows
> of zeros at the top. What I need is a function for my macro that will
> go through the top of the sheet, leaving the header row alone, and
> delete the rows that have a value (not content) of zero.
>
> I have tried various code examples (ie, getCellByPosition), but it
> doesn't work (unexpected syntax errors)
>
> All I want to do is dump the value of a cell (ie $A$2) into a string
> variable so that I can compare it. Why does it have to be so hard?
>
> rk

No time to elaborate, but if speed is an issue, you might consider using
the getDataArray() method for a cell range and then traversing the data,
it is likely to be faster...

--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
My Book: http://www.hentzenwerke.com/catalog/oome.htm
Info:  http://www.pitonyak.org/oo.php
See Also: http://documentation.openoffice.org/HOW_TO/index.html


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Getting Cell Value in a Macro

Terry North
In reply to this post by Russell King-2
Russell King <rjkfsm <at> bluebottle.com> writes:

.... What I need is a function for my macro that will go
> through the top of the sheet, leaving the header row alone, and delete
> the rows that have a value (not content) of zero.
...
> rk
>
I composed this before the master (Andrew P.) posted.  No liability accepted.
It may need altering.  It may help.  Regards.
        Do

        oRange1 = oSheet.getCellRangeByPosition _
                ( 0, 4, 255, 4 )  'this gets A5 to IV5
        lVolume = oRange1.computeFunction _
          ( com.sun.star.sheet.GeneralFunction.SUM ) ' this gets the total
                                                     'of values in the range
        If lVolume = 0 Then
          oSheet.Rows.RemoveByIndex( 4, 1 ) 'removes row 5
        End If

        Loop until lVolume > 0




---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Getting Cell Value in a Macro

Terry North
Terry North <terrencenorth <at> yahoo.com.au> writes:

...
> Loop until lVolume > 0
>
On further reflection this could lead to a never-ending loop if you have a row
with a minus value.  Should therefore be Loop until lVolume <> 0




---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Getting Cell Value in a Macro

Andrew Pitonyak
In reply to this post by Russell King-2
Russell King wrote:

> Hi,
>
> I am trying to build a macro that will build a synopsis sheet from the
> other sheets in a spreadsheet. The synopsis is over 1600 rows long, so
> it is a bit tedious to do it by hand and since each sheet changes not
> just the data, but the amount of data, the macro ends up putting in a
> lot of references to cells that are empty in order to make sure it
> gets it all. The macro then sorts the data which leaves over 600 rows
> of zeros at the top. What I need is a function for my macro that will
> go through the top of the sheet, leaving the header row alone, and
> delete the rows that have a value (not content) of zero.
>
> I have tried various code examples (ie, getCellByPosition), but it
> doesn't work (unexpected syntax errors)
>
> All I want to do is dump the value of a cell (ie $A$2) into a string
> variable so that I can compare it. Why does it have to be so hard?
>
> rk

The following macro searches the first column and removes all rows that
contain a zero value. You can search a different column by changing the
lLeft and lRight values shown below.

Sub RemoveZeroRows
  Dim oCell
  Dim oSheet
  Dim oCursor
  Dim oData()
  Dim oRow()
  Dim oRows
  Dim n As Long

  oSheet = ThisComponent.getSheets().getByIndex(0)
  REM Start with cell A2... Skip the first row
  oCell = oSheet.GetCellbyPosition( 0, 1 )
  oCursor = oSheet.createCursorByRange(oCell)
  oCursor.GotoEndOfUsedArea(True)

  REM If there is a lot of data, then perhaps I can get the
  REM last used row and use that to inspect only a single column.
  REM This should be faster and use less memory.
  Dim oRange
  Dim lLeft As Long   : lLeft   = 0
  Dim lTop As Long    : lTop    = 1
  Dim lRight As Long  : lRight  = 0
  Dim lBottom As Long : lBottom = oCursor.RangeAddress.EndRow

  oRows = oSheet.getRows()

  REM Get the data for column A, excluding the first row (becuase lTop is 1)
  oRange = oSheet.getCellRangeByPosition(lLeft, lTop, lRight, lBottom)
  oData() = oRange.getDataArray()

  REM Move through the list BACKWARDS. This keeps the rows in synch
  REM with the data array.
  For n = UBound(oData()) To LBound(oData()) Step -1
    oRow() = oData(n)
    If oRow(0) = 0 Then
      oRows.removeByIndex(n + lTop, 1)
    End If
  Next
End Sub

--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
My Book: http://www.hentzenwerke.com/catalog/oome.htm
Info:  http://www.pitonyak.org/oo.php
See Also: http://documentation.openoffice.org/HOW_TO/index.html


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]