Need help getting this basic macro working

English
,
#1

Attached is a link to my test sheet (I hope it contains the actual macro I am trying to get to work).
First thing is I would select an option from a data validation list in cells A3 to A10.
When I am done making the selection I want to add all of my selection from the data validation cells to the next set of available empty cells in the TalOwn set of cells. I have a macro (that others on here helped me develop) in the Push Button to transfer all the names from cells A3 through A10 (but only whatever cells are filled in) to the TalOwn set of cells.
I have two issues:
Issue 1: The macro in the button does not work at all and I get the error “Wrong Number of Parameters”, no idea why.
Issue 2: If I go into edit macros and run the macro from there it sort of works, but it only moves the first cell (A3) to the TalOwn list and does not do anything with the rest of the names in Cells A4 through A10.
EDIT: Even if Cell A3 is empty it still does not transfer any of the other fields over.

If the macros are not copied in the link I will paste them below in a code block.
Link to the Calc sheet.
Test.ods (16.0 KB)

#2

Please, attach your file directly in this site.

#3

I didn’t know there was a way to attach a file here. So many different forums have so many different rules and ways of doing things. Most of the forums I belong to do not allow direct file uploads nor do they allow direct image insertion, you have to provide a link from somewhere. Providing a link is usually the most normal way to do things.
The file is attached to the original post.

#4

This file not have any macro.
image

#5

If you are not familiar with this Ask site you might find This is the Guide… to be a useful resource.

#6

I will put the macro in a reply below. Thank you for pointing that out.

#7

@ elmau
This is the macro that is supposed to be in the sheet.

REM  *****  BASIC  *****
Option Explicit

Sub PutDataIntoFirstEmptyRowOfTheTargetRange2
 Dim oDoc as object
 Dim oSourceRange as object
 Dim oSourceCell as object
 Dim oTargetRange as object
 Dim oTargetCell as object
 Dim lColNr as long
 Dim sFormula as string  	
	oDoc = ThisComponent
	oSourceRange = GetNamedRange("TalList", oDoc)
	oSourceCell = oSourceRange.ReferredCells.getCellByPosition(0,0)
	sFormula = oSourceCell.Formula			
	oTargetRange = GetNamedRange("TalOwn", oDoc)
	lColNr = 0 'zero based numbering
	oTargetCell = GetFirstEmptyCellInAColumnOfRange(oTargetRange, lColNr)
	oTargetCell.Formula = sFormula
	oSourceCell.Formula = ""
end Sub
'_____________________________________________________________________________________________


Function GetNamedRange2(sRange_name as string, optional oCalc_File as object) as object
 Dim oDoc, oRange as object
	If IsMissing(oCalc_File) then
		oDoc = ThisComponent
	else
		oDoc = oCalc_file
	end if 	
	If len(sRange_name) = 0 or sRange_name = "" then
		GetNamedRange = NOTHING
 		Exit function
 	End if
 	if oDoc.namedranges.hasByName(sRange_name) then 
		oRange = oDoc.NamedRanges.getByName(sRange_name)
		GetNamedRange = oRange
	else
		GetNamedRange = NOTHING
	end if		
end function
'_____________________________________________________________________________________________


Function GetFirstEmptyCellInAColumnOfRange2(oTheRange as object, lColNr as long) as object
 Dim oCells as object
 Dim oCell as object
 Dim lRowNr as long 
 Dim i as long 	
 	oCells = oTheRange.ReferredCells
 	lRowNr = oCells.Rows.Count 	
 	For i = 0 to lRowNr-1
 		oCell = oCells.getCellByPosition(lColNr,i)
 		If  oCell.getType() = 0 Then 
 			GetFirstEmptyCellInAColumnOfRange = oCell
 			Exit function
 		end if 		
	next i	
end function
'_____________________________________________________________________________________________
#8

Fixed!
oTheMovingTest.ods (22.6 KB)

1 Like
#9

Thanks, it works great in the sample sheet but for some reason when I transfer it to my regular sheet it doesn’t work. When I run it from the macros console I get the following error:

BASIC runtime error. Argument is not optional.

The line it quotes is this one:

If len(sRange_name) = 0 or sRange_name = "" then4

I have no idea why it’s not working on my regular sheet.

#10

Work fine in my system. Please tell us your LibreOffice version and OS.

Version: 24.2.2.2 (X86_64) / LibreOffice Community
Build ID: 420(Build:2)
CPU threads: 16; OS: Linux 6.8; UI render: default; VCL: gtk3
Locale: es-MX (en_US.UTF-8); UI: en-US
24.2.2-2
Calc: threaded
#11

It’s LibreOffice 24.2.2.2 and I am on Windows 11 Pro (up to date).
It might be something in my settings as the test sheet runs fine but as soon as I put it in my regular sheet it gets the error I mention above.
EDIT: I get the error above only when I run it in the macro box, but if I run it in the button I get the following error:
A Scripting Framework error occurred while running the Basic script Standard.Module2.GetFirstEmptyCellInAColumnOfRange.

Message: wrong number of parameters! at C:/cygwin64/home/buildslave/source/libo-core/scripting/source/basprov/basscript.cxx:199

#12

What event you set in control button?

#13

⇒ … PutDataIntoFirstEmptyRowOfTheTargetRange2
You seem to be a bit overwhelmed with choosing the right routine out of three :rofl:

That wouldn’t have happened with a single simple routine in python:

def move_to_empty_Targets_Rows(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    named = doc.NamedRanges
    source = named.TalList.ReferredCells
    target = named.TalOwn.ReferredCells.queryEmptyCells()[0]
    data = source.FormulaArray
    out = [[entry[0]] for entry in data if entry[0] ]
    target[ :len(out), 0 ].FormulaArray = out
    source.clearContents(4)
#14

I take it that’s the same thing in python?
If so do I just implement it into the macro like I would Basic or is there more involved?

#15

Execute Action is the event the macro is under. It’s set the same as the other two buttons with macros in them.

#16

I am still having trouble getting this macro to work in my sheet.
I have copied it exactly as you posted it above and assigned it to the button and I do not get the error anymore except it only moves and clears out the first cell in the range “TalList”, on the test sheet you sent me it copies and clears the full range and I don’t understand why it isn’t working on my sheet.
I did find one difference between your test sheet and my sheet.
On your test sheet in the button where you “Assign Action” the name of the macro you have is: test_move.py$move_to_empty_Targets_Rows (document, Python)
But on my sheet (even though I copied it exactly) it shows up as this when I “Assign Action”
Standard.Module1.PutDataIntoFirstEmptyRowOfTheTargetRange2 (application, Basic)
Now in this I had to select the very last macro in that set for it to even move one. So I am assuming it is only running that single macro out of the three. If that is the case how do I get it to run all three in the button?

That is literally

#17

See in your new topic

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.