Public Function RigaFineSomma(level As Range, answerLoc As Range) As Currency
Application.Volatile
AllowUdfs = True
'Forces Excel to recalculate all values when a
'cell changes
'Variables
Dim row, col, CurrentLevel As Integer
Dim Sum As Double
Sum = 0
'set starting summation row and column cell locations based on input
'Levellocations
row = level.row + 1
col = level.Column
'set current row Level based on input Level value
CurrentLevel = level.Value
'set the cell to gather the sums from, should be the same and return
'Location
colTotal = answerLoc.Column
'interate over all rows below current
Do While (Cells(row, col) > CurrentLevel)
'increment the row and loop
row = row + 1
Loop
'return total sum
RigaFineSomma = row - 1
End Function
Public Function RigaFineSommaP(level As Range, answerLoc As Range) As Currency
'la funzione ritorna la riga del (primo) codice padre
'con livello = quello della riga corrente (Level) - 1
Application.ScreenUpdating = True
Application.EnableEvents = True
AllowUdfs = True
Application.Calculation = xlManual
'Forces Excel to recalcuate all values when a
'cell changes
'========================================================='
'Generates sum of all values in "answerLoc2"
'for all rows where the "Level" is one increment higher
'========================================================='
'Variables
Dim row, col, CurrentLevel As Long
Dim Sum As Double
Sum = 0
On Error GoTo CONTINUA
'set starting summation row and column cell locations based on input
'Levellocations
' seleziona le righe dal livello 0 (end-item) a quella precedente la cella attiva
row = level.row - 1
col = level.Column
'set current row Level based on input Level value
CurrentLevel = level.Value
'set the cell to gather the sums from, should be the same and return
'Location
colTotal = answerLoc.Column
'interate over all rows below current
Do While (Cells(row, col) >= CurrentLevel)
'decrementa the row and loop
row = row - 1
Loop
Application.Calculation = xlAutomatic
'return total sum
RigaFineSommaP = row
'ricalcola la funzione in tutte le celle del foglio attivo
'evita copia-valori dopo Invio su formula somma.se
'in celle di altro foglio della stessa cartella
Exit Function
CONTINUA:
End Function
Villeroy wrote:This forum is about a completely different software product. Nobody here runs Excel.
marocdea wrote:Villeroy wrote:This forum is about a completely different software product. Nobody here runs Excel.
Thank you. I know, but some people may find useful this code to translate it into a macro for OpenOffice Calc
marocdea wrote:it works fine in the excel, in attachment.
it does'nt work if you have BOM Level and part number columns given by two Excel formulas, with cell number format.
Users browsing this forum: No registered users and 12 guests