[OpenOffice]Macro deletes data cells
Tisoon
Posted messages
15
Status
Member
-
Tisoon Posted messages 15 Status Member -
Tisoon Posted messages 15 Status Member -
Good evening,
I am using OpenOffice.org and I would like to create (or rather complete) a macro to delete cells containing data. I am using Microsoft Office's VBA language for my macro, starting it with:
Option VBASupport 1
allowing the language to be recognized by OpenOffice.
I have searched everywhere with the help of my friend Google for solutions to my problem (as I do for almost everything I want to do with macros...), but I am completely stuck on this one. However, the documentation on this subject is plentiful.
But it doesn't always suit the problem; sometimes it involves deleting data from a row/column, sometimes deleting specific cells. I have tried to use many of these suggestions by attempting to adapt them, but I always run into an error. I tried selecting and then deleting, but without success either. I even tried to remove instead of delete, but the same issue arose. I'm sure I'm going about adapting them incorrectly, a solution that seems all the more possible since I don't understand these delete functions at all (I had classes at university for what I managed to do).
Here is my macro in its current state:
I have included a little screen to illustrate the situation a bit more:
http://www.noelshack.com/
In fact, there is a whole list of products, but I want only those with stock to appear here. That is what I have managed to write. However, over time, it often varies and I may find myself with fewer products having stock. That is where I am stuck, as I don't know how to automatically remove the unnecessary lines (for example: I no longer have dresses, when I rerun the macro, "Dresses" will disappear (along with quantity and price) and thus "Wheat Bags" will drop down one, but it will end up duplicated (lines 21 and 22)).
I would therefore like these repeated lines to disappear. I don't know if it is possible to add this to the end of the macro or if I should approach it differently. Otherwise, in a less elegant method, I could say to delete everything from A12 to C100 before launching the macro that summarizes the stock. But I don't know how to do that either...
Thank you in advance for the help you will provide me with.
Best regards, Tisoon
Configuration: Windows 7 / Firefox 5.0.1
I am using OpenOffice.org and I would like to create (or rather complete) a macro to delete cells containing data. I am using Microsoft Office's VBA language for my macro, starting it with:
Option VBASupport 1
allowing the language to be recognized by OpenOffice.
I have searched everywhere with the help of my friend Google for solutions to my problem (as I do for almost everything I want to do with macros...), but I am completely stuck on this one. However, the documentation on this subject is plentiful.
But it doesn't always suit the problem; sometimes it involves deleting data from a row/column, sometimes deleting specific cells. I have tried to use many of these suggestions by attempting to adapt them, but I always run into an error. I tried selecting and then deleting, but without success either. I even tried to remove instead of delete, but the same issue arose. I'm sure I'm going about adapting them incorrectly, a solution that seems all the more possible since I don't understand these delete functions at all (I had classes at university for what I managed to do).
Here is my macro in its current state:
REM ***** BASIC ***** Option VBASupport 1 Sub Stock y = 2 x = 12 Do If (Sheets("Synthèse").cells(y,17) > 0) then Sheets("Synthèse").cells(x,1) = Sheets("Synthèse").cells(y,16) Sheets("Synthèse").cells(x,2) = Sheets("Synthèse").cells(y,17) Sheets("Synthèse").cells(x,3) = Sheets("Synthèse").cells(y,18) x = x + 1 End if y = y + 1 Loop until (Sheets("Synthèse").cells(y, 17) = "") End Sub I have included a little screen to illustrate the situation a bit more:
http://www.noelshack.com/
In fact, there is a whole list of products, but I want only those with stock to appear here. That is what I have managed to write. However, over time, it often varies and I may find myself with fewer products having stock. That is where I am stuck, as I don't know how to automatically remove the unnecessary lines (for example: I no longer have dresses, when I rerun the macro, "Dresses" will disappear (along with quantity and price) and thus "Wheat Bags" will drop down one, but it will end up duplicated (lines 21 and 22)).
I would therefore like these repeated lines to disappear. I don't know if it is possible to add this to the end of the macro or if I should approach it differently. Otherwise, in a less elegant method, I could say to delete everything from A12 to C100 before launching the macro that summarizes the stock. But I don't know how to do that either...
Thank you in advance for the help you will provide me with.
Best regards, Tisoon
Configuration: Windows 7 / Firefox 5.0.1
1 answer
-
Hello
Since the compatibility of macros in VBA for Microsoft Office is quite limited under OpenOffice, I suggest the following that should work:
Option VBASupport 1 Sub new_Stock() y = 2 x = 12 Do Sheets("Synthèse").Cells(x, 1) = "" Sheets("Synthèse").Cells(x, 2) = "" Sheets("Synthèse").Cells(x, 3) = "" x = x + 1 Loop Until (Sheets("Synthèse").Cells(x, 1) = "") x = 12 Do If (Sheets("Synthèse").Cells(y, 17) > 0) Then Sheets("Synthèse").Cells(x, 1) = Sheets("Synthèse").Cells(y, 16) Sheets("Synthèse").Cells(x, 2) = Sheets("Synthèse").Cells(y, 17) Sheets("Synthèse").Cells(x, 3) = Sheets("Synthèse").Cells(y, 18) x = x + 1 End If y = y + 1 Loop Until (Sheets("Synthèse").Cells(y, 17) = "") End Sub
--
Always zen-
Thank you for your response.
At first, I had searched in this direction, but without success, I had tried to make it too complicated. However, this macro does not work for a reason that escapes me.
When I execute it, at the line:
Sheets("Synthèse").Cells(x, 1) = ""
I get an error message indicating:
"Runtime error BASIC.
'35'
Sheets"
Looking on the internet, I came across this:
https://docs.microsoft.com/fr-fr/previous-versions/visualstudio/visual-studio-2008/s8y42ktc(v=vs.90)?redirectedfrom=MSDN
However, after reading what is written there, I do not see the connection with the macro. Everything seems perfectly spelled to me.
Thank you for the help you are giving me.
Best regards, Tisoon -
Hello,
It works perfectly on my end under Calc, otherwise I wouldn't have posted it on the forum. I haven't found any of the usual Excel erasing functions that work under Calc, so I've used "basic".
However, I think it's not a solution to try to run Excel macros under Calc because there are far too many non-functional procedures.
It would be better to work with the language specific to OpenOffice, but after testing a bit, I didn't continue because their Basic is really a nightmare, and I was afraid it would overwhelm me.
You provided a link to Microsoft, but the problem comes from OpenOffice since this macro works under Excel too. It is, however, perfectly normal to declare variables, and personally, I always put "Option Explicit" to avoid any typing errors.
However, it's the Sheets that Calc doesn't recognize, and this is probably due to Open Basic which requires defining every element of a command before executing it. The code I shared with you is exactly the same as yours except that we clear each cell instead of documenting it.
-
It's true that the macro still works, but in reality only my part, and you are right in saying that you took the same functions as I did, which is why I'm confused...
I initially wanted to use the OOo language as well, but since I didn't understand anything, I tried to get Excel language to work.
Thank you anyway, and I will continue to look on my side, just in case!
Thanks again.
Best regards, Tisoon
-
