Need help with a Macro in Basic that will take a result from one cell and put it in the next available cell in a range

This is a redo from a previous question but the answer given was in Python and the other macros I have are all in Basic.

In my worksheet on the “Start” page I have a selection (Data Validation) list in J12 . When I find the selection I want, I want to be able to activate the macro that will put the selection into the next empty cell in a range of cells that go from J13 to J36, then clear the selection in J12 (but not remove the formatting or completely delete the full selection list). By clear the selection it will go to the empty field included in the selection list.

Do you mean this one?

…is there any chance this can be done in basic

of course it can, if you had a minimal clue about Basic, you could translate it yourself,

Please study the API function of the LO. You had got many sample code snippets - based on the StarBasic - at your another question . Most of the are usable in this task too.

I suggest you to download, install and use the MRI or XrayTool. The you will able to examine the existing properties, methods, and interfaces of the programming objects. And study the API descriptions, and Andrew Pitonyak’s free macro books.
Then you will able to GET some cell ranges; to GET the cells in the range one-by-one (cyclically); to CHECK if the cells are empty or not, and to PUT a value into the cell by your macro subroutine.
I suggest you to use Form Control Element for this task instead of the Data Validity feature: then you will assign the macro to an event of the Form Control.

I don’t have time to do all that. I am just trying to get this sheet to work. No, I do not have any real understanding of Basic or much of any other programming language, just snippets of things here and there.
There was another thread where I asked about adding numbers in and I asked if I could use something like that and someone did respond but they put it in Python.
I want to put the macro in a button because I do not want it to activate unless I decide to use it or not. Just because a selection is made does not mean it will be what is needed.

What?! Are you waiting someone who complete YOUR job?

What? Not even sure how you got that in this. This is something I am doing at home for myself not in any way job related.

In this case: do not use the macros.

I already have a few macros in my sheet and it wouldn’t do what I want it to do without them.

Here is an another approach with named cells and named cell ranges. Those are independent from the Sheet name and from the sheet number in the LibreOffice.

Fill a range with selected data.ods (11.7 KB)

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

Sub PutDataIntoFirstEmptyRowOfTheTargetRange
 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("mySourceCell", oDoc)
	oSourceCell = oSourceRange.ReferredCells.getCellByPosition(0,0)
	sFormula = oSourceCell.Formula			
	oTargetRange = GetNamedRange("myTargetRange", oDoc)
	lColNr = 0 'zero based numbering
	oTargetCell = GetFirstEmptyCellInAColumnOfRange(oTargetRange, lColNr)
	oTargetCell.Formula = sFormula
	oSourceCell.Formula = ""
end Sub

Function GetNamedRange(sRange_name as string, optional oCalc_File as object) as object
 Dim oDoc, oRange as object
	If IsMissing(oCalc_File) then
		oDoc = ThisComponent
		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
		GetNamedRange = NOTHING
	end if		
end function

Function GetFirstEmptyCellInAColumnOfRange(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

I just had the chance to take a peek at this and this might work the best. I was able to see where you named the selection cell and the range. I should be able to work with this. I may try and get this implemented this Saturday after work (overtime for 5 hrs this weekend). I will let you know how it works out and if I have any issues. Thank you very much for this.

I actually checked and tried it last night and it works just fine. The only difference is I had to set the button activation method under the Events tab to “Execute Action” as it would throw an error if I used “When mouse button pressed”.

Thanks for this!

Lert me tell a joke.

It’s really a simple thing. I can’t understand why the colleague is on strike. The only problem I see is that we probably didn’t understand the work order properly. Apologies!
As soon as you have studied my solution, you will surely agree, and see that I did it within a few minutes.

However, I would recommend to work with a ComboBox instead of “>Data>Validity”.
See attached example.
disask95924simpleMagicTrick.ods (24.8 KB)

1 Like

That’s interesting! I will have a look to see if I can figure this out when I get the chance. It might not be until tomorrow or later in the week that I get the chance to look at it. Thanks so far!

I was finally able to look at it this evening. First thing I noticed is that it works automatically upon making a selection in the list, which does not work for my idea. I do not want it to add the selection to the range unless I activate the macro. The second thing I noticed is that I am unable to tell how your code identifies the list and selection or how it knows what cells to put that data in. My list and the corresponding range that it will fill have moved from their original spots because I am trying to reorganize the sheet and probably will do so again.

The connection to a source range for the listed data, and the bound cell
where the chosen string goes to, are features of the ListBox. Edit it, and you will see.
The range where the history shall be created is given (as a range name) via the .Tag property of the ListBox object which is presented a “Additional Information” in the editor.

I added two solutions for your wish to have a “choice if the choice” shall go to the history.
The code behind is rather raw. One solution uses a hyperlink created with the HYPERLINK() function to call the respective Sub, the pther one now actually has a Button for this.

Be aware of the fact that addresses used in FormControl objects can’t automatically adapt to movements.

disask95924simpleMagicTrick.ods (26.5 KB)