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!
[Solved] Macro for Relative Rows or Columns
[Solved] Macro for Relative Rows or Columns
Last edited by daveg7 on Fri Sep 29, 2017 2:15 am, edited 2 times in total.
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
Re: Macro for Relative Rows or Columns
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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Macro for Relative Rows or Columns
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?
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?
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
- MrProgrammer
- Moderator
- Posts: 4907
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Macro for Relative Rows or Columns
You cannot "remove Column W". Column W always exists. Columns A through AMJ always exist.daveg7 wrote:Also, is there any way to name the entire Column Z so that the macro continues to work even if I remove Column W?
Tools → Macros → Recorddaveg7 wrote: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.
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)
Tools → Macros → Recorddaveg7 wrote:If I'm in any column, I need to be able to move the cursor to row 40,000.
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
Last edited by MrProgrammer on Wed Sep 27, 2017 9:25 pm, edited 4 times in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Macro for Relative Rows or Columns
I also did not understand what you said in the last sentence.
A highlight desired cells on the worksheet is not very difficult:
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
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English