SOLVED creating equations in macro

[Fred1]

1. I want to go to the end of existing data (ctrl end) then
   cell right and create the equation =(contents of cell left)/const.
   if i record this it places the result in cell left, i.e.
   overwrites which creates an an error.

[JohnSUN]

May be so?

Sub InsertFormulaInLastCellOfActiveSheet()
Const divider = 5 REM # Change your constant here
Dim oActiveSheet As Variant
Dim oCursor As Variant
Dim addrRange As New com.sun.star.table.CellRangeAddress
Dim inCell As Variant
Dim outCell As Variant
Dim sSourceCellName As String
	oActiveSheet = ThisComponent.getCurrentController().getActiveSheet()
	oCursor = oActiveSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	addrRange = oCursor.getRangeAddress()
	inCell = oActiveSheet.getCellByPosition(addrRange.EndColumn, addrRange.EndRow)
	sSourceCellName = inCell.AbsoluteName
	outCell = oActiveSheet.getCellByPosition(addrRange.EndColumn+1, addrRange.EndRow)
	outCell.setFormula("="+sSourceCellName+"/"+divider)
End Sub

[Fred1]

Hi JohnSUN

Thanks for the excellent response

Well that sorts out where it puts the result, and is more or less understandable, unlike record macro. But it places an absolute address =$Import.$C$402/ so I can’t carry out the next step which is to copy that cell to the column above it.

Should have explained that in the first place, I can’t see how to use relative addressing with

outCell = oActiveSheet.getCellByPosition(addrRange.EndColumn+1, addrRange.EndRow)

outCell.setFormula("="+sSourceCellName+"/"+divider)

[JohnSUN]

Try rewrite line with calculation of sSourceCellName as sSourceCellName = Join(Split(inCell.AbsoluteName,"$"),"")

[Fred1]

Superb Thanks a lot and I’ve learnt something about macros.