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

Conditional autoinput of values to named range

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


Joined: 25 Aug 2009
Posts: 26
Location: Järvenpää

PostPosted: Fri Jul 16, 2010 8:52 am    Post subject: Conditional autoinput of values to named range Reply with quote

Here is a routine with witch you can update values on named range KohdeAE by changing values of named range ArvoAE if corresponding cell in EhtoAE range is TRUE. For example if values in ArvoAE are daily results, condition "=ArvoAE<KohdeAE" in every cell of in corresponding row of EhtoAE give all time minimun values in KohdeAE.

Even all those ranges can be in different sheets easies way is to use same upper row number for all, use only one column for each range and keep source range (ArvoAE ) smallest of three or keep all same size.

Condition in an EhtoAE cell can be empty but then there is no update for corresponding value in KohdeAE. These conditions need not to depend any way on values of named ranges but they are quite freely selectable. For instance condition "=DATEVALUE("2010-07-17")=TODAY()" register latest value of one particular day if any.

It depends situation how this macro is useful to assign. If updating of source (ArvoAE) values is fast and automatic then it may be wise to assign to change of modified status. Same time it must ensure that conditions change false every time after update of target values (KohdeAE). Otherwise there is continuous updating(*). Typically this is the case if there is equal sign "=" in the condition clause.

(*) Edit: My mistake, it is not so bad situation after all. Only state is after every save instantly as modified again.

Regards
Risto

Code:


Sub ArvoEhdollisesti
REM Kohde-alueen arvo päivitetään vastaavalla Arvo-alueen arvolla, jos Ehto-alueella on TOSI-arvo
REM Alueet ArvoAE, EhtoAE ja KohdeAE tulee olla nimetty aiemmin Calcissa
REM Em. alueet voivat olla eri taulukkolehdilläkin
REM  - kutsuttu alirutiini: GetGlobalRangeByName
REM (RJ)

DIM  oArvot as Object, oEhdot as Object, oKohteet as Object
DIM lSarakeLkm as Long, lRiviLkm as Long, i as long, j as Long
   
   oArvot = GetGlobalRangeByName("ArvoAE")
   
   oEhdot = GetGlobalRangeByName("EhtoAE")

   oKohteet = GetGlobalRangeByName("KohdeAE")
   
   REM Kiitokset Sasa Kelecevicille seuraavista silmukoista
   REM suoritetaan päivitys ehdon mukaan
   lSarakeLkm = oArvot.Columns.getCount() -1
   lRiviLkm = oArvot.Rows.getCount() -1
   For i = 0 to lSarakeLkm
      For j = 0 to lRiviLkm
         IF oEhdot.getCellByPosition (i,j).Value then
            oKohteet.getCellByPosition (i,j).Value = oArvot.getCellByPosition (i,j).Value
         EndIf
      Next j
   Next i

End Sub      'ArvoEhdollisesti

REM Author: Rob Gray
REM Email: robberbaron@optusnet.com.au
REM Modified from a macro contained in Andrew Pitonyak's document.
REM This makes it easier to transition from VBA global ranges and to
REM separate parameters onto a Config sheet
Function GetGlobalRangeByName(rngname As String)
  Dim oSheet       'Sheet containing the named range
  Dim oNamedRange  'The named range object
  Dim oCellAddr    'Address of the upper left cell in the named range
  DIM sLehdennimi as String
 
  If NOT ThisComponent.NamedRanges.hasByName(rngname) Then
    MsgBox "Sorry, the named range !" & rngname & _
           "! does not exist","MacroError",0+48
    Exit function
  End If
 
  REM The oNamedRange object supports the XNamedRange interface
  oNamedRange = ThisComponent.NamedRanges.getByName(rngname)
  'Print "Named range content = " & oNamedRange.getContent()
 REM modified by (RJ)         =====================
 sLehdennimi= NimenLehti(oNamedRange)
 oSheet = ThisComponent.Sheets.getByName(sLehdennimi)
  REM Get the com.sun.star.table.CellAddress service
  'oCellAddr = oNamedRange.getReferencePosition()
 
  REM Now, get the sheet that matters!
  'oSheet = ThisComponent.Sheets.getByIndex(oCellAddr.Sheet+1)
  REM End of modification of (RJ)   =====================
  GetGlobalRangeByName = oSheet.getCellRangeByName(rngname)
 
  REM now can apply .GetCellByPosition(0,0).string  etc
End function

Function NimenLehti(Aluenimi) as String
REM Palauttaa Aluenimen taulukkolehden
DIM sTaul(4) as String

   sTaul=Split(Aluenimi.Content,".")
   NimenLehti=Mid(sTaul(0),2)
End Function    'NimenLehti
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 Code Snippets 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