Ask Your Question
0

which code in basic to select the first empty cell in a column to insert a value? [closed]

asked 2020-03-28 18:15:19 +0100

RudPiva gravatar image

I'm making a form to fill out a sheet automatically, but I dont know the code to select de firt empty cell.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by RudPiva
close date 2020-04-11 20:52:12.696976

Comments

How do you tell your "macro" what column to search for the "first empty cell"?
Will it start above the filled cells? Amidst the filled cells? Below the filled cells?
(Is "first" related to a specific direction of search?)
Is there an assurance concerning the multitude of filled ranges within the column?
In short: Words are quickly found and even more quickly used.
Coding an automatism you need exactly defined terms or you will have to do it again and again, every time something doesn't work as expected in a given case.

Lupp gravatar imageLupp ( 2020-03-28 23:27:53 +0100 )edit

I wish the Code will select row 2 in column A, and change to next, when I insert new datas.

RudPiva gravatar imageRudPiva ( 2020-03-29 17:19:56 +0100 )edit

I did it that way.

Sub insert
Dim oCurrentController As Variant
Dim oActiveSheet as variant
dim oObj1 as variant
dim aRangeAddress As New com.sun.star.table.CellRangeAddress
dim nEndRow as long
dim oSheets as variant
Dim oObj2 as variant
dim oCellRangeByName as variant


oCurrentController = ThisComponent.getCurrentController()
oActiveSheet = oCurrentController.getActiveSheet()
oObj1 = oActiveSheet.createCursor()

oObj1.gotoEndOfUsedArea(True)
aRangeAddress = oObj1.getRangeAddress()
nEndRow = aRangeAddress.EndRow

oSheets = ThisComponent.getSheets()
oObj2 = oSheets.getByIndex(0)
oCellRangeByName = oObj2.getCellRangeByName("F1").Value
oCellRangeByName2 = oObj2.getCellRangeByName("F2").Value

ThisComponent.Sheets(0).getCellByPosition(0,nEndRow+1).value = oCellRangeByName
ThisComponent.Sheets(0).getCellByPosition(1,nEndRow+1).value = oCellRangeByName2

End Sub
RudPiva gravatar imageRudPiva ( 2020-03-31 15:32:51 +0100 )edit

But now, I wish copy a value from form text box instead from cell.

RudPiva gravatar imageRudPiva ( 2020-03-31 15:43:46 +0100 )edit

You didn't answer to my question in return posted in my first comment.
You simply disregarded the suggestion by @mauricio without commenting on it.
Who then should worry about your new question posted as a comment now?

New question, new topic is the rule.

Commenting on what you presented as your solution:
You get the nEndRow having moved your cursor to the EndOfUsedRange in the ActiveSheet. Then you use this value as if it is the EndRow in the first sheet (Sheets.getByIndex(0)). Anyway you don't worry about which coulmn is relevant for the EndRow question nor do you give any assurances insofar. What's the idea of this strange proceeding?

Then: What do you mean by "text box" now? Graphíc shapes of the TextBox type rarely occur in spreadsheets, and if they do it should be suspected to be bad design.

Any way: How do you ...(more)

Lupp gravatar imageLupp ( 2020-03-31 17:30:57 +0100 )edit

Sorry!! My first code. I found this solution anyway:

Sub 
Dim oCurrentController As Variant
Dim oActiveSheet as variant
dim oObj1 as variant
dim aRangeAddress As New com.sun.star.table.CellRangeAddress
dim nEndRow as long

oCurrentController = ThisComponent.getCurrentController()
oActiveSheet = oCurrentController.getActiveSheet()
oObj1 = oActiveSheet.createCursor()

oObj1.gotoEndOfUsedArea(True)
aRangeAddress = oObj1.getRangeAddress()
nEndRow = aRangeAddress.EndRow

oTextfield1 = oDialog1.getControl("TextField1")
oTextfield2 = oDialog1.getControl("TextField2")
oTextfield3 = oDialog1.getControl("TextField3")
oTextfield4 = oDialog1.getControl("TextField4")

ThisComponent.Sheets(0).getCellByPosition(0,nEndRow+1).string = oTextfield1.text
ThisComponent.Sheets(0).getCellByPosition(1,nEndRow+1).string = oTextfield2.text
ThisComponent.Sheets(0).getCellByPosition(2,nEndRow+1).string = oTextfield3.text
ThisComponent.Sheets(0).getCellByPosition(3,nEndRow+1).string = oTextfield4.text

End Sub
RudPiva gravatar imageRudPiva ( 2020-04-03 15:35:10 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-03-28 18:34:25 +0100

try:

Sub next_cell()

    cell = ThisComponent.CurrentSelection
    cell = get_next_cell(cell)

    MsgBox cell.AbsoluteName

End Sub


Function get_next_cell(source) As Object
    cursor = source.SpreadSheet.createCursorByRange(source)
    cursor.gotoEnd()
    cell = source.SpreadSheet.getCellByPosition(source.RangeAddress.StartColumn, cursor.RangeAddress.EndRow +1 )
    get_next_cell = cell
End Function
edit flag offensive delete link more

Comments

Thanks!! I tried it, but it wasn't what I was looking for.

RudPiva gravatar imageRudPiva ( 2020-04-03 15:28:07 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2020-03-28 18:15:19 +0100

Seen: 194 times

Last updated: Mar 28 '20