I have a few cells with forumlas that update from reading a file when opening the document.
But sometimes, I can allow a user to enter a new value (that is not yet available on the file) to get better result.
The problem is if the user saves it, then the formula is lost. I can replace the cell contents with a simple macro string replacment, but that gets treated as text and not a formula. What am I doing wrong?
Code: Select all
sub ReplaceFormula
dim document as object
document = ThisComponent
'100's - on the 2nd sheet
sheet = document.Sheets(1)
sheet.getCellByPosition(2, 2).String = "=SUMPRODUCT(('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$O$2:INDEX(data.$O:$O,COUNTA(data.$O:$O))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$P$2:INDEX(data.$P:$P,COUNTA(data.$P:$P))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$Q$2:INDEX(data.$Q:$Q,COUNTA(data.$Q:$Q))>=4)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$R$2:INDEX(data.$R:$R,COUNTA(data.$R:$R))>=4)=4)"
'75's
sheet = document.Sheets(1)
sheet.getCellByPosition(2, 3).String = "=SUMPRODUCT(('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$O$2:INDEX(data.$O:$O,COUNTA(data.$O:$O))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$P$2:INDEX(data.$P:$P,COUNTA(data.$P:$P))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$Q$2:INDEX(data.$Q:$Q,COUNTA(data.$Q:$Q))>=4)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$R$2:INDEX(data.$R:$R,COUNTA(data.$R:$R))>=4)=3)"
'50's
sheet = document.Sheets(1)
sheet.getCellByPosition(2, 4).String = "=SUMPRODUCT(('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$O$2:INDEX(data.$O:$O,COUNTA(data.$O:$O))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$P$2:INDEX(data.$P:$P,COUNTA(data.$P:$P))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$Q$2:INDEX(data.$Q:$Q,COUNTA(data.$Q:$Q))>=4)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$R$2:INDEX(data.$R:$R,COUNTA(data.$R:$R))>=4)=2)"
'25's
sheet = document.Sheets(1)
sheet.getCellByPosition(2, 5).String = "=SUMPRODUCT(('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$O$2:INDEX(data.$O:$O,COUNTA(data.$O:$O))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$P$2:INDEX(data.$P:$P,COUNTA(data.$P:$P))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$Q$2:INDEX(data.$Q:$Q,COUNTA(data.$Q:$Q))>=4)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$R$2:INDEX(data.$R:$R,COUNTA(data.$R:$R))>=4)=1)"
'0's
sheet = document.Sheets(1)
sheet.getCellByPosition(2, 6).String = "=SUMPRODUCT(('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$O$2:INDEX(data.$O:$O,COUNTA(data.$O:$O))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$P$2:INDEX(data.$P:$P,COUNTA(data.$P:$P))=1)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$Q$2:INDEX(data.$Q:$Q,COUNTA(data.$Q:$Q))>=4)+('file:///C:/Users/e754356/Downloads/SurveyReport.xls'#$Sheet1.$R$2:INDEX(data.$R:$R,COUNTA(data.$R:$R))>=4)=0)"
end sub