Palo Community Forum

Hi,

I don't now, if this is the right topic for this. So if not please move it to the right place.

I have wrote a macro to reset all Palo Formulas in a sheet.
It helps if a user has destroy one or more Palo Formulas in the input aera.
The marco also looks for values in the cell and put it to the database. So aslo this get not lost.

I paste the macro here, because without the helf from the forum, it whould not exist ;-)
So thanks to all who have help me or had answer similar topics.
Maybe it is usefull for some others too.
Attantion: it is not perfect!! ...but it work ... ;-)

Here my code:


Sub All_Palo_Formel_reset()

'diese Macro geht Felder durch und prüft ob die Paloformel noch stimmt.
'this marco resets all Palo DATAC Formulas
'ist eine Paloformel fehlerhaft
'it also correct wrong formulas
' wird der Wert aus der Zelle in der Datenbank abgelegt und die Palo Formel wieder hergestellt.
'the value from the cell is written to the database


Dim strcoll_letter As String 'Spalte als Buchstabe, coloumn as letter
Dim strformel As String 'Paloformel für die Zelle
Dim intzeile As Integer 'aktuelle Zeile als integer, actual row as integer
Dim wert As Variant 'wert aus der Zelle, value from the cell
Dim strzelle As String 'zellennamen als string z.B. E30, cellname
Dim alteformel As String 'Formel die aktuell in der Zelle, actual formula
Dim varhelp As Variant 'help variable
Dim arraycol As Variant
Dim vonzeile As Integer 'first row from the Palo input area
Dim biszeile As Integer 'last row of the Palo input formula


'coloums thar shoudl be proved, coloums with Palo input formuals
arraycol = Array("E", "Y", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX")

vonzeile = 31 'erste Zeile mit Palo Formeln, first row input area
biszeile = 54 ' letzte Zeile mit Palo Formeln, last row input area
strcoll_letter = "E" 'start position (nicht unbedingt nötig)
'ACHTUNG beim anpassen den Macros muß der Index der Titelzeile (hier 30) und der Namensspalte (hier C) noch angepasst werden.
'Attantion: in this example C is the column with the Palo row-names, it has to be modified in other sheets and here is 30 the row with the coloumn names, that also has to be modified

For s = LBound(arraycol) To UBound(arraycol) 'den array durchlaufen
strcoll_letter = arraycol(s)
For i = vonzeile To biszeile

intzeile = i
'die richtige Formel zusammen setzen:
'bulid the formula (here is 30 and C !! and YOUR Dimensons )
strformel = "=PALO.DATAC($A$1;$A$2;$A$3;$A$4;$A$5;$A$6;$C" & CStr(intzeile) & ";" & strcoll_letter & "$30)"
'Die Zellbeizeichnung:
'cell name:
strzelle = strcoll_letter & CStr(intzeile)
'Formel aus der Zelle
'actual formula from the cell:
alteformel = Range(strzelle).FormulaLocal
'Vergleich aktuelle Formel und die richtige Formel:
'comparing right and actual Palo Formula:
If (alteformel <> strformel) Then ' there is a mistake in the actual formula or it is missing

'den Wert aus der Zelle in die Palo Datenbank übertragen:
'save the value from the cell in the database:
wert = Range(strzelle).Value
' here also look at YOUR dimensions and names row and names coloumn:
varhelp = Application.Run("PALO.SETDATA", wert, False, Range("A1").Value, Range("A2").Value, Range("A3").Value, Range("A4").Value, Range("A5").Value, Range("A6").Value, Range("C" & CStr(intzeile)).Value, Range(strcoll_letter & "30").Value)
'die alte Paloformel wieder herstellen
'reset the Palo formula
Range(strzelle).FormulaLocal = strformel

End If
Next i 'nächste zeile, next row
Next s
Worksheets("Eingabe").Calculate ' calulate the sheet

'Erfolgsmeldung, happy ending message:
MsgBox ("Alle Paloformeln im Eingabebereich wurden wieder hergestellt.")

End Sub

best wishes,
Karen

Thank for the addion :-)

My users have a lot of fantasy to destroy or change my Palo Forumlas and i'm not every day in office.
The macro is the "help yourself" solution additional to all other potection ;-)

best wishes,
Karen