I wasted a lot of time using AI for macros. I apreciate your help but I think in our case, AI has nothing to do with wasting your time. (It is rather that I was not clear enough explaining what I was asking for: sorry for that.) I can imagine that repair some AI-“product” can be difficulter as to make it from the beginning but I was not about repairing “AI-macro”.
1 Like
That’s a welcome insight.
I didn’t understand your real problem from the beginning, and I don’t now.
Least of all I understood the subject (“short” question), and I still don’t understand it.
In what way are cells “connected” to something?
I shouldn’t have stepped in at all.
Are we now about real problem - or about its short description? If we are about real problem, I thought it is now clear. “It was only after I looked at the Anki website that I could imagine what you actually wanted”. According to that, I don´t know which information should I now provide.
I think it is completely normal that I am not familiar with macros and you are not familiar with systems like Anki. If I am not familiar with macros, it is not easy to summarize my problem in as few words in the subject, thought I understand that good subject is a clue. I am not about charity and I am considering to pay for the macro,but before visiting somebody, it would be useful to know ho which expectations I can have.
Here is a first lousy proposal without any Makrocode and so far no any logic for »present this question not until some date in future«
pw: 1234 and 12345
anki_reload.ods (10.3 KB)
Thank you but my goal is different. I try to answer questions. If I know the answers, I make nothing in Calc. If not, I write f.e. “10” so that the question will be reviewed exactly in ten days: it should move to the corresponding date.
Nice, but what should happen if, for example, you are unable to complete your exercises in exactly 10 days?
It can be handled without LibreOffice and happens not so offen, so I can remember it. The past days in the first row would be manually deleted only after I am sure that allways was done und that there are any words under them.
I’m sorry, but I’m not going to bother trying to fix the business logic in your broken template.
The only thing left to do here is to determine which questions should no longer be asked, or rather, when!
It turned out not much longer than what the AI offered you. Since you won’t use this code to learn BASIC programming anyway, I didn’t try to make it understandable. Since I’m not much different from ChatGPT in this, I’ll say it the way it usually says - “this version of code really cannot fail”
Option Explicit
Sub MoveTaggedRows
Dim oDoc As Variant
Dim oSheets As Variant
Dim oActiveSheet As Variant, oCursor As Variant, oSourceData As Variant, oColumn As Variant
Dim AnalysisCell As Double
Dim sNameActiveSheet As String, sErrMsg As String
Dim oMoveInfo As Variant
Dim iRow As Long, iColumn As Long, i As Long, iSheet As Long, iCol As Long
Dim oSheet As Variant, oData As Variant, oRange As Variant
Dim aRangeAddress As New com.sun.star.table.CellRangeAddress
Dim aCellAddress As New com.sun.star.table.CellAddress
oDoc = ThisComponent
oSheets = oDoc.getSheets()
Rem Get the current sheet and all its data
oActiveSheet = oDoc.getCurrentController().getActiveSheet()
sNameActiveSheet = oActiveSheet.getName()
oCursor = oActiveSheet.createCursor()
oCursor.gotoEndOfUsedArea(True)
oSourceData = oCursor.getDataArray()
Rem Collect information about the places of movement
oMoveInfo = Array()
For iRow = LBound(oSourceData) To UBound(oSourceData)
For iColumn = 1 To UBound(oSourceData(iRow)) Step 5
AnalysisCell = oSourceData(iRow)(iColumn)
If AnalysisCell > 45700 Then ' Maybe this is a date from one of the adjacent sheets
oRange = oActiveSheet.getCellRangeByPosition(iColumn, iRow, iColumn+3, iRow)
AppendToArray(oMoveInfo, Array(AnalysisCell, oRange))
EndIf
Next iColumn
Next iRow
Rem Find target sheet
For iSheet = 0 To oSheets.getCount()-1
oSheet = oSheets.getByIndex(iSheet)
If oSheet.getName() <> sNameActiveSheet Then
oData = oSheet.getRows().getByIndex(0).getData()
For i = LBound(oMoveInfo) To UBound(oMoveInfo)
If UBound(oMoveInfo(i)) < 2 Then
AnalysisCell = oMoveInfo(i)(0)
For iCol = 0 To UBound(oData(0))
If oData(0)(iCol) = AnalysisCell Then
AppendToArray(oMoveInfo(i), iSheet)
AppendToArray(oMoveInfo(i), iCol)
Exit For
EndIf
Next iCol
EndIf
Next i
EndIf
Next iSheet
Rem Move if found
For i = LBound(oMoveInfo) To UBound(oMoveInfo)
If UBound(oMoveInfo(i)) < 2 Then
sErrMsg = sErrMsg & oMoveInfo(i)(1).getCellByPosition(0,0).getString() & " in " & oMoveInfo(i)(1).AbsoluteName & Chr(13)
Else
oSheet = oSheets.getByIndex(oMoveInfo(i)(2))
oColumn = oSheet.getColumns().getByIndex(oMoveInfo(i)(3))
oSheet.getColumns().getByIndex(6)
aCellAddress = oColumn.queryEmptyCells().getByIndex(0).getCellByPosition(0, 0).getCellAddress()
oSheet.moveRange(aCellAddress,oMoveInfo(i)(1).getRangeAddress())
EndIf
Next i
If sErrMsg <> "" Then MsgBox sErrMsg,MB_OK, "Not matched dates:"
End Sub
Rem 🔧 Pomocné funkce:
Sub AppendToArray(oData As Variant, ByVal x As Variant)
Dim iLB As Long, iUB As Long
iLB = LBound(oData, 1)
iUB = UBound(oData, 1) + 1
ReDim Preserve oData(iLB To iUB)
oData(iUB) = x
End Sub
Update However, AI does not always say stupid things. For example, I was bored writing explanations for this code, so I asked Mentor to explain in detail what I wrote there. Here is what he said:
Explanation of the VBA code
Variable Declaration
- The code starts by declaring and initializing several variables to store different types of data such as documents, sheets, arrays, strings, and integers.
Retrieving Data
- It then retrieves the active document and its sheets.
- It gets the active sheet and its name.
- It creates a cursor to navigate to the end of the used area in the active sheet and retrieves the data array from that area.
Processing Data
- It iterates over the data array to check for values greater than 45700.
- If a value is found, it creates a cell range based on the position of that value and stores it in an array along with the value.
Matching Data
- It then loops through all the sheets except the active one.
- For each sheet, it retrieves the data from the first row.
- It compares the stored values with the data in the first row of each sheet to find a match.
- If a match is found, it updates the stored array with additional information about the matched cell.
Moving Data
- Finally, it loops through the stored array of matched data.
- For each matched data, it either displays a message with the cell information or moves the cell to a new location within the sheet.
- If there are unmatched dates, it displays a message with the unmatched cell information.
Custom Function
- The code includes a custom function
AppendToArraythat appends a value to an array.
Conclusion
- In summary, the code searches for specific values in a spreadsheet, matches them with data in other sheets, and either displays information about the cells or moves them to new locations based on certain conditions.
@sokol92
I’m definitely not, every AI has a broader knowledge base, but even as a mediocre Python programmer, I have more expertise in this limited area!
@fpy : I would recommend refraining from such completely pointless comments in the future.
1 Like
Thank you for the macro. It seems not to work yet , or I am confused about what I am missing. I opened my template published here, sheet Explication. I deleted contents of K19, L19,M19, n19 (as here were the expected result). The message in the box (macro doesn´t start running at all): wrong number of parameters! Then I reopen the fil, run macro without any changes of gile, again “wrong number of parameters” I gave the code to AI and asked: Can this have something to do with the structure, cells, values of the file on where the macro is applied (and not with tha macro itself)? Just to check what I can modify. It sais not (but it would be very strange,you surelly tested the macro). I tried to replace the same code (maybe I made something wrong when copying) in organizer of macros (delete-paste), exactly,from O to B. Now, mistake iLB = LBound(oData, 1) - 73 - argument is not optional. I asked the same question: Can this have something to do with the structure, cells, values of the file on where the macro is applied (and not with tha macro itself)? Again, it sais not. Again,it would be very strange.
Also at the very beginning of my todays attempts (before “wrong number of parameters”), it run but stopped at errors. I suspected that this errors had something to do with format of cell and AI “confirmed” it. I tried to verify by the cells that are “expected to cooperate” (B13, K19): I write =TYPE(B13) or =TYPE(K19), it is 1. I know but that big portion of problems are connected with format of cells but here it should be OK.
I am completele confused. Excuse me for unclear expressions but I am just common user trying to do my best.
would I ? 
The message about the wrong number of parameters may appear if you try to run AppendToArray instead of MoveTaggedRows
Which one did you try?
AppendToArray is marked as Rem 
Helped funkce in the code comments, and Mentor said the same thing about it.