Forum at OOoForum.orgThe 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 Forum Index -> Code Snippets
View previous topic :: View next topic  
Author Message
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.



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

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
      Next j
   Next i

End Sub      'ArvoEhdollisesti

REM Author: Rob Gray
REM Email:
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 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

End Function    'NimenLehti
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic Forum Index -> 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