Let’s start by throwing away your code and forget about it. All constructs that were used in this macro are guided by Excel architecture and VBA methods. Calc is not Excel, everything is done differently here.
First of all - opening a spreadsheet. You can use several methods, they are described in the Wiki
I recommend using the
Function OpenDocument(DocPath as String, Args(), Optional bDisposable as Boolean)
from the Tools standard library: load the library, make sure the desired file exists at the specified location, try opening the desired file, verify that the file actually opened. In Basic it is written like this:
GlobalScope.BasicLibraries.LoadLibrary("Tools")
sFileURL = ConvertToURL("Z:\CPG\CPG Roswell\IS Dispatch\Dispatch report.xlsx")
If Not FileExists(sFileURL) Then Exit Sub
oDoc = OpenDocument(sFileURL, Array())
If IsNull(oDoc) Then Exit Sub
Getting the desired sheet is also described in Wiki
oSheet = oDoc.Sheets.getByName("NJ")
Now let’s think about whether you need an Autofilter? Hint: You don’t need an AutoFilter for this task. Second tip: AutoFilter is not needed at all to work, it gives more problems than convenience.
And how to complete the task without it?
For example, you can go through all the lines on the sheet, reading the values of each cell in the 13th column and checking its contents for the desired text - if the desired row is found, check the contents of the 4th cell of this row and, if necessary, enter the desired word. This will work. But! This will work VERY SLOWLY. It is better to use another method: read the entire table into an array at once, loop through the elements of the array and then write it all back to the table at a time.
nLastRow = GetLastUsedRow(oSheet)
a13 = oSheet.getCellRangeByPosition(12,0,12,nLastRow).getDataArray()
aRange = oSheet.getCellRangeByPosition(3,0,3,nLastRow)
a4 = aRange.getDataArray()
For i = 1 To nLastRow
If a13(i)(0) = "BUSINESS SPECIFIC SOFTWARE~43230000" Then
If a4(i)(0) = "=" Then If a4(i)(0) = "CPG_HECTOR"
EndIf
Next i
aRange.setDataArray(a4)
I hope these explanations are enough for you to be able to write the whole subroutine?
