Help with change to working Calc code

English
#1

Hi, I have working Cal code which moves a set of numbers
(formatted as text) (eg. 2-20 or 9-44) down one cell and also increases the last number in the set by one. So as an example if 2-20 was in cell B30 and the macro was run 2-21 would be written to cell B31. The issue is the 2-20 (text) in cell B30 stays in the cell after it has been moved down one cell but I would like to have it deleted.

If you could please take a look at the code and see what is needed to make the change. I can upload the spreadsheet if that helps. Please let me know.

Thank-you.

  • I’m using Cal Version: 6.4.7.2

  • OS: Linux

Sub MOVE10PLUSDOWN()
Dim rng As Range, tmp, i As Long
'THE NUMBER SET COLUMN
Set rng = Range("B23:B45")
tmp = rng.Cells(rng.Cells.Count).Value
For i = rng.Cells.Count To 2 Step -1
rng.Cells(i).Value = Increment(rng.Cells(i - 1).Value)
Next i
rng.Cells(1).Value = Increment(tmp)

End Sub
'If value has a dash, increment the second number



Function Increment(v)
Dim rv, arr
rv = v
arr = Split(v, "-")
If UBound(arr) = 1 Then rv = arr(0) & "-" & CLng(arr(1)) + 1
Increment = rv

End Function
#2

Add

rng.Cells(i - 1).clearContents(5)

right after the line

rng.Cells(i).Value = Increment(rng.Cells(i - 1).Value)

To see what the ‘5’ is about, go to Deleting All Types of Contents from Calc Range using Macro

This is a standard way to make things move in computer science…copy then delete.

#3

Hi joshua4

Thanks for your reply. No excuses but I’m just changing over to Linux and to Libreoffice so needless to say a lot to learn. In my problem I posted the wrong code. Same problem with not deleting. I you have a monument I sure would appreciate looking at my code.
Bob

Sub MOVE10PLUSDOWN()

Dim oSheets As Variant, oSheet As Variant
Dim oCellRangeByName As Variant, oDataArray As Variant
Dim i As Long, tmp As Variant
oSheets = ThisComponent.getSheets()
If oSheets.hasByName(“SHEET 1”) Then
oSheet = oSheets.getByName(“SHEET 1”)
oCellRangeByName = oSheet.getCellRangeByName(“B23:B45”)
oDataArray = oCellRangeByName.getDataArray()
tmp = oDataArray(UBound(oDataArray))(0)
For i = UBound(oDataArray) To LBound(oDataArray)+1 Step -1
If Trim(oDataArray(i)(0))="" Then
oDataArray(i)(0) = Increment(oDataArray(i-1)(0))
EndIf
Next i
oDataArray(0)(0) = Increment(tmp)
oCellRangeByName.setDataArray(oDataArray)

    EndIf

End Sub
'If value has a dash, increment the second number
Function Increment(v)
Dim rv, arr
rv = v
arr = Split(v, “-”)
If UBound(arr) = 1 Then rv = arr(0) & “-” & CLng(arr(1)) + 1
Increment = rv
End Function

#4 
If Trim(oDataArray(i)(0))="" Then
    oDataArray(i)(0) = Increment(oDataArray(i-1)(0))
Else
   oDataArray(i)(0) = ""
EndIf
#5

Hi John

I’m sure no expert on code but yours always looks really good and of course always works. I really appreciate it.

While I have you can I ask if I should be using LibreOffice version 7. I recently switched from Windows to Linux Mint and they are are using LibreOffice version 6. Is there a difference when running code in Calc or not much?

Have a great day
Bob

#6

No, not much at all, the code will work the same in these versions.

(I want to apologize for the delay in answering the last question - I tried to read and understand all the code for your project from the 10 plus down.ods file. Didn’t succeed :frowning: )

#7

Hi John

A few month’s ago you helped me with some code. It works great but I have a small problem and was wondering if you could, once again, help. The code searches for sets of numbers (10-25 or 1-44) in the range B23:B45 and it moves each set of numbers down one cell and also increases the the second number by 1. It then clears the contents in the original cell. To show you what happens now:

Cell B23 has 10-25 - when the code is run it writes 10-26 in cell B24 and clears content in cell B23.

If Cell B23 has 10-25 and 1-44 is in cell B24 the code writes 1-45 in cell B25 and just deletes 10-25 in cell B23. I need both sets of numbers to remain in the range.

My desired result would be 10-26 in cell B24 and 1-45 in cell B25 and clear contents in cell B23.

I can send you a spreadsheet if this isn’t clear. I hope you can help. Thank you.

#8

Hi Bob! Haven’t seen you for a long time.
I was sure that you had already completed the translation of your project from VBA to BASIC.
No, you don’t need to send a spreadsheet, everything we need is already in this discussion.
Your problem today has a simple solution. Take the code from that comment of yours and just remove the line
If Trim(oDataArray(i)(0))="" Then
and its corresponding
EndIf
Isn’t that what you wanted?

#9

That’s so much for your assistance. I’m got delayed on converting my code but I’m back at it again, slowly. I’m sorry to bother you again with this one but the code is just not happy with the change. I suspect it’s me. Here is the code with the change:

Sub MOVE10PLUSDOWN()

Dim oSheets As Variant, oSheet As Variant
Dim oCellRangeByName As Variant, oDataArray As Variant
Dim i As Long, tmp As Variant
oSheets = ThisComponent.getSheets()
If oSheets.hasByName(“SHEET 1”) Then
oSheet = oSheets.getByName(“SHEET 1”)
oCellRangeByName = oSheet.getCellRangeByName(“B23:B45”)
oDataArray = oCellRangeByName.getDataArray()
tmp = oDataArray(UBound(oDataArray))(0)
For i = UBound(oDataArray) To LBound(oDataArray)+1 Step -1
oDataArray(i)(0) = Increment(oDataArray(i-1)(0))

Next i
oDataArray(0)(0) = Increment(tmp)
oCellRangeByName.setDataArray(oDataArray)

EndIf

End Sub
'If value has a dash, increment the second number
Function Increment(v)
Dim rv, arr
rv = v
arr = Split(v, “-”)
If UBound(arr) = 1 Then rv = arr(0) & “-” & CLng(arr(1)) + 1
Increment = rv
End Function
#10

Not criticism, but by way of streamlining, you might note that unless you have to work with objects or in some cases array data types, you can assign directly to the Function name.

Function Increment(v As Variant) As Variant
   Dim arr
   arr = Split(v, "-")
   If UBound(arr) = 1 Then
      Increment = arr(0) & "-" & (CLng(arr(1)) + 1)
   Else
      Increment = v
   EndIf
End Function
#11

Hi Joshua

Thanks for your reply. I’m sorry for the delay in getting back to you. I tried your suggestion but the problem is as follows:

If cell B23 has 1-1
Cell B24 has 2-1
Right now when I run the code it writes 2-2 in cell B25 and deletes 1-1 and 2-1 in cells B23 and B24. I would like it to write 1-2 in cell B24 and 2-2 in cell B25. Hope this is a small change in the code.
Thank-you.

#12

At first face, your macro seems to do what you are expecting. I did have to change the double-quote characters to work with my LO BASIC IDE.

I’ve attached a spreadsheet that

  1. tests the Increment function by showing multiple generations of Increment without using the MOVE10PLUSDOWN subroutine. This is wired by simple cell addressing.
  2. offers a test region and test button for trying MOVE10PLUSDOWN.

See if this helps you resolve the issue.

SplitIncrement.ods (31.7 KB)

#13

Hi Joshua

Tried the code and it works perfectly!! Thank you so much.
Greatly appreciated
Bob

#14

Sorry if this is a little bit patronizing, but that your code basically worked goes to show a principle of developing code that seems to be very hard-won by most non-professional coders:

It is good, right, and common to spend over half your coding time coding for testing.

While I’m on the soap box, the next thing would be to notice the indenting, spacing, etc., in the spreadsheet code provided. Which leads to:

It is good, right, and common to spend almost a quarter of your coding time making your code simple, logical, and readable…but not clever or optimized. Readability is at least 10 times more important than optimization for all but 10% of code.

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