CODEWITHSUNDEEP
vbaexcellibreoffice
user2449942
user2449942

Reputation: 11

Sorting data from 1 column to 3

I'm trying to use a macro in libre-office to look for a string in a cell - if said cell contains that string, the program should go to the cell below it, cut the contents of that cell and paste it into the next column and then go down one row further than before, cut those contents and place them in the column after the next.

So if A1 contains said string, the script should go to A2, cut that cell and paste it's content to B1, go to A3 cut those contents and paste those to C1 and then it should check A4.

Hope this makes sense. What I've got so far is this:

Sub Neuorganisieren
name = "check"
    If find(". Fr" ,A1,0)>1 then
        Selection.Offset(1, 0).Select
        cut
        Selection.Offset(-1, 1).Select
        paste
        Selection.Offset(2, -1).Select
        cut
        Selection.Offset(-2, 2).Select
        paste
        MsgBox ("found it")
    Else
        MsgBox ("no")
    End If
End Sub

However, I keep getting the error that the sub-procedure/function procedure is not defined.

Upvotes: 1

Views: 81

Answers (1)

ASattar
ASattar

Reputation: 82

Try this:

Sub Neuorganisieren()
Name = "check"
    If InStr(Range("A1").Value, ". Fr") > 0 Then
        Selection.Offset(1, 0).Select
        Selection.Cut
        Selection.Offset(-1, 1).Select
        Selection.Paste
        Selection.Offset(2, -1).Select
        Selection.Cut
        Selection.Offset(-2, 2).Select
        Selection.Paste
        MsgBox ("found it")
    Else
        MsgBox ("no")
    End If
End Sub

  1. InStr checks to see if the value exists anywhere in the contents of A1. If it does, it kicks off the rest of the if statement.

  2. You can't cut and paste by themselves. You must have an object to apply it to, in this case, the selection. So we use Selection.Cut instead of just Cut.

I didn't quite understand the "business" logic behind what you're aiming to do so I just went after correcting the existing code so I'm not sure if this will do what you intend, but the code itself will work.

Hope this helps.

Abdul

Upvotes: 1

Related Questions