ristoi
General User
Joined: 25 Aug 2009
Posts: 24
Location: Järvenpää
|
 Posted: Fri Jul 16, 2010 8:52 am Post subject: Conditional autoinput of values to named range |
 |
|
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
|
|
|
