[Solved] Macro for Relative Rows or Columns

[daveg7]

This is for a macro. If I'm in any column, I need to be able to move the cursor to row 40,000. Similarly, if I'm in any row, move the cursor to column Z.

I tried memorizing the keystrokes from Navigator, but I get only one absolute reference for the starting column or row. I need a macro that works from any current column or row.

(Easy to do this in Excel's VBA language.)

Thanks!

[FJCC]

I sounds like you are trying to record a macro, which limits what you can do pretty severely. It is easy to get a reference to a cell in row 40,000 or column Z using the API (Application Programming Interface) in OpenOffice Basic or in Python if you want to use a more modern language. Getting that reference does not "move the cursor" if you mean the visible cursor on the screen. Moving the cursor is rarely necessary in API macros. If you explain more about what you are trying to do, I or someone else can help you get started on an API macro.

[daveg7]

PROJECT A:
I've got a column of text, and maybe there are some spaces, and I just want to cursor to appear for the user after the last item. I was planning to go in the same column, to a row that is far below any possible input and then simply hit Control-Up.

PROJECT B:
In any given row, I want the cursor to appear for the user, for example in Column Z, on the same row so that she can then make an entry there.

Also, is there any way to name the entire Column Z so that the macro continues to work even if I remove Column W?

[MrProgrammer]

Also, is there any way to name the entire Column Z so that the macro continues to work even if I remove Column W?

You cannot "remove Column W". Column W always exists. Columns A through AMJ always exist.

In any given row, I want the cursor to appear for the user, for example in Column Z, on the same row so that she can then make an entry there.

Tools → Macros → Record
Home (moves active cell to column A of current row)
Right Arrow
Stop Recording
Give it a name, say ColZ
Tools → Macros → Organize → Basic → ColZ → Edit
Change "args2(0).Value = 1" to "args2(0).Value = 25" (25 columns to the right of column A is column Z)
Save (on toolbar) then close the macro editor

[Tutorial] Binding a macro: Shortcut key, menu or toolbar
[Tutorial] How to record a macro (and Regular Expressions)

If I'm in any column, I need to be able to move the cursor to row 40,000.

Tools → Macros → Record
Shift+Home
Edit → Copy
⌘Home on a Mac or Ctrl+Home on other platforms
Edit → Paste
Edit → Undo
Shift+Tab
Down Arrow
Stop Recording
Give it a name, say Row40000
Tools → Macros → Organize → Basic → Row40000 → Edit
Remove "rem" in "rem dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())"
Change "args7(0).Value = 1" to "args7(0).Value = 39999"
Save (on toolbar) then close the macro editor

[JohnSUN-Pensioner]

I also did not understand what you said in the last sentence.

A highlight desired cells on the worksheet is not very difficult:

Code: Select all

Sub GoToRow
Dim oCSelection As Variant
Dim oCell As Variant
Dim aRangeAddress As New com.sun.star.table.CellRangeAddress
Dim oSheet As Variant
Dim oColumn As Variant
Dim oEmptyRanges As Variant
	oCSelection = ThisComponent.getCurrentSelection()
Rem Selected more than one range? Take the first one
	If oCSelection.supportsService("com.sun.star.sheet.SheetCellRanges") Then _
		oCSelection = oCSelection.getByIndex(0)
	aRangeAddress = oCSelection.getRangeAddress()
	oSheet = oCSelection.getSpreadsheet()
	oColumn = oSheet.getColumns().getByIndex(aRangeAddress.StartColumn)
	oEmptyRanges = oColumn.queryEmptyCells()
	If oEmptyRanges.getCount() = 0 Then 
		Print "There is no more empty cells in this column"
		Exit Sub 
	EndIf 
	aRangeAddress = oEmptyRanges.getByIndex(oEmptyRanges.getCount()-1).getRangeAddress()
	oCell = oSheet.getCellByPosition(aRangeAddress.StartColumn, aRangeAddress.StartRow)
	ThisComponent.getCurrentController().Select(oCell)
	ThisComponent.CurrentController.Select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
End Sub

Sub GoToColumn
Const COLUMN_NAME = "Z"
Dim oCSelection As Variant
Dim oCell As Variant
Dim aRangeAddress As New com.sun.star.table.CellRangeAddress
Dim oSheet As Variant
Dim oColumn As Variant
Dim oEmptyRanges As Variant
	oCSelection = ThisComponent.getCurrentSelection()
Rem Selected more than one range? Take the first one
	If oCSelection.supportsService("com.sun.star.sheet.SheetCellRanges") Then _
		oCSelection = oCSelection.getByIndex(0)
	aRangeAddress = oCSelection.getRangeAddress()
	oSheet = oCSelection.getSpreadsheet()
	oCell = oSheet.getCellRangeByName(COLUMN_NAME & (aRangeAddress.StartRow+1))
	ThisComponent.getCurrentController().Select(oCell)
	ThisComponent.CurrentController.Select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
End Sub