Need help changing working code


I have working code that I need help making a small change to and was wondering if someone could assist. Currently the code writes to cell A14 and B14 I would like the removal of writing to cell B14 and so only A14 would occur. Also, the code at Step 4 (below) needs to be changed. The change I need is to find the text number in the cell below the positive result. In my example in the spreadsheet it finds a match in cell D28 so it would go to the cell below, cell D29 in my example and write the number 7 to cell A14. I’ve attached the spreadsheet.

Even if you only have time for the first change that would be greatly appreciated. Bob

The current code does the following:

(Step 1) The code starts with the number in cell A1 looking for a match in the range A20:A34,D20:D100,I20:I100,N20:N100. A positive match occurs only when the code finds the number from A1 plus text in the first cell to right of a matched number (formatted as text). In the sample spreadsheet A1 has a 9 in it and there is a 9 in cell I28 but no text in cell J28 so it’s a false result.

(Step 2) In cell D28 it’s a positive result because of the text numbers in cells E28.

(Step 3) Next the code goes to the cell to the right of it and uses the first number 10. The code then searches for a match in the range M1:M15 and writes the second number in the set of numbers 25 in the next available cell to the right of it and would continue the process in future writes. In my example 25 would be written in cell N9.

(Step 4) The code then would write the first number in the set of numbers, (10 in my sample) to either cell A14. If cell A14 has a number in it write it to cell B14.

(Step 5) The code then clears content. In my sample spreadsheet that would be cells E28, F28, G28 and H28 would be cleared.

option explicit

Sub Find
on local error goto bug
dim oDoc as object, oSheet as object, addr1(), i&, j&, sA1$, oRange1 as object, oCell2 as object, s1$, s2$, data1(), oRange2 as object, oCellE as object, sE$
addr1=array("A20:A34", "D20:D109", "I20:I109", "N20:N109")
for i=lbound(addr1) to ubound(addr1)
for j=lbound(data1) to ubound(data1) 'iterate numbers from cells A (D, I, N)
if s1=sA1 then 'numbers is the same like from A1
oCell2=oSheet.getCellByPosition(oRange1.RangeAddress.StartColumn+1, oRange1.RangeAddress.StartRow+j) '1st cell to right (column B, E etc.)
if testValue(s2) then
writeToM(oSheet, s2)
writeToA14(oSheet, s2) 'write to cells A14 or B14
if i=0 then 'column A is iterated
oCellE=oSheet.getCellByPosition(oRange2.RangeAddress.StartColumn+1, oRange2.RangeAddress.StartRow+j)
if s2<>sE then oCell2.string="" 'A28 isn’t equal to E28 so clear A28 - else
oRange2=oSheet.getCellRangeByPosition(oCell2.CellAddress.Column, oCell2.CellAddress.Row, oCell2.CellAddress.Column+3, oCell2.CellAddress.Row)
end if
end if
end if
next j
next i
goto final
msgbox(Error & chr(13) & "Line: " & Erl & chr(13) & "Err: " & Err, 16)
final: 'turn on the Screen Update and AutoCalculate
End Sub

Sub writeToM(oSheet as object, s$)
dim oRange as object, i&, oCell as object, sM$, data(), j&, p()
p=split(s, "-")
for i=lbound(data) to ubound(data)
if sM=p(0) then
oCell=oSheet.getCellByPosition(oRange.RangeAddress.StartColumn+j, oRange.RangeAddress.StartRow+i)
loop while oCell.string<>""
with oCell
.ParaAdjust=1 'Align Center
end with
end if
next i
End Sub

Sub writeToA14(oSheet as object, s$) 'write to A14/B14
dim oCell as object, p()
p=split(s, "-")
oCell=oSheet.getCellRangeByName("A14") 'A14
if oCell.string<>"" then 'A14 isn’t empty so get the cell B14
oCell=oSheet.getCellByPosition(oCell.CellAddress.Column+1, oCell.CellAddress.Row) 'B14
end if
with oCell
end with
End Sub

Function testValue(s$) as boolean 'return True if the string s$ agrees with the specified regular expression
dim pole(2), oFind, oFindParam, oFound, oStart, oEnd, iCount%
oFind=CreateUnoService("") 'searching for string
With oFindParam
.searchString="(^\d±\d+$)" 'Number(s)-Number(s)
End With
oFound=oFind.searchForward(s, 0, len(s))
if iCount=-1 then testValue=false else testValue=true
End Function

FIND FIX.ods (10.2 KB)

[erAck: edited to surround code with ``` lines for proper display, see This is the guide - How to use the Ask site? - #6 by erAck , also replaced typographic double quotes with ASCII double quote characters for correctness. .searchString="(^\d±\d+$)" seems wrong, whatever the original intention of ± was.]

Thanks for engaging the community.

But we need to work on this request, I think, to get good, quick results.

  1. Give a very quick overview of what you are trying to do–what is this for? Otherwise it is like an exam in a programming class.
  2. Label your columns in the example ODS in ways that indicate what is supposed to happen in each column (that is, partial column range as you have set up).
  3. Include your macro(s) as they are in the ODS. We’ll look at them before allowing macros, anyway. That way they are formatted and ready to test.
  4. If you do post any macro pieces in a request, be sure to format them useing the slash-in-angle-brackets icon here in the editing pane.

Hi Joshua

Is there a way to delete or mark the request cancelled? I can’t seem to find anything on the site. I haven’t had any replies. I will try some other sites.

Hi Joshua

I received a reply from PKG that I can’t seem to reply to. I’m a retired guy that is just trying to figure everything out on this site, code, etc. and my head is spinning. I just switched to Libreoffice recently and really like it. The switch from Excel has been very challenging in terms of macros for me. The change I need help with is, as far I know, is a small change. Would you know someone who could assist me?
Thank you

Remove the above code lines from the function writeToA14 and it will always write to A14, which means it will always overwrite the value of A14 when it wants to.

Change this to
oCell2=oSheet.getCellByPosition(oRange1.RangeAddress.StartColumn, oRange1.RangeAddress.StartRow+j+1) '1st cell below

I would use Rem in front of the original and change a copy of the line then try it out

Rem oCell2=oSheet.getCellByPosition(oRange1.RangeAddress.StartColumn+1, oRange1.RangeAddress.StartRow+j) '1st cell to right (column B, E etc.)

oCell2=oSheet.getCellByPosition(oRange1.RangeAddress.StartColumn, oRange1.RangeAddress.StartRow+j+1) '1st cell below

Give it a shot.

Hi Joshua

Thank you so much for the help. I’m really short on time today but will check it out on Thursday and get back to you.

Hi Joshua

I tried the line above and there are no errors but the code just does nothing. I rem the first line so that is not the issue. I haven’t tested the second line of code because I need the first issue to be resolved first. If you have some more time to look at it again I sure be appreciative.

Could you edit the above comment if you would, and include an upload of the file as you have it right now?

Hi Joshua

Just uploaded the file. I’m sure your code works but it’s something on my end. I just wish I knew what is wrong. I’ve spent many hours on the weekend and I’m no further ahead. Do I have put the code in a file? I saw that suggestion. Any feedback sure would be appreciated.
Thank you.

ASK FIND FIX.ods (10.2 KB)

I cannot logically understand the request to perform the functions asked for but still look below (the 7, say) rather than to the side. The system needs to see a form like 12-78 to perform its operations.

I have it working as best as I can imagine. Post what the goal is if this doesn’t do the trick, but you may have to take a deep dive into BASIC to finish this one up.

ASK FIND FIX 2.ods (14.4 KB)

Hi Joshua4

Thank you for the changes to my code. It’s really close. You said the code needs to see: The system needs to see a form like 12-78 to perform its operations. That’s what has to be changed.
The only correction is the write to cell A14 it should be a 7. I need the code to write the number below a positive result. In my spreadsheet it’s a positive result in cell D28 so the code would go to the cell below that and write 7 (in this example) to cell A14. If you could please make that change it would fix everything.

Thank you for your help.


ASK FIND FIX 2.ods (14.0 KB)

I added on to the code to separately look at the hyphenated values and the value below the lookup value. I think that’s what you want. Give it a try.

ASK FIND FIX 21.ods (15.1 KB)

That’s it!!

Thank you so much for your time and effort. This stuff is never easy, for me anyways. Have a great weekend.

Thanks again

Hi joshua4

I’m really sorry to bother you but in my haste I forgot to check on the Clear Contents. In your changes I see the following code:
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, “.uno:ClearContents”, “”, 0, Array())

I deleted the rem but I can’t get the code to delete the four cells. A1 is the search number and when found do the two writes and then ClearContents. I really hope this is a very small ask. Uh man. Thanks

Clear is only meant to clear the tracking values in the M column area. The other cells are either overwritten by you or the program, anyway, each time. I’m afraid I simply do not understand

Perhaps it would be better to use the spreadsheet you last sent me titled, " ASK FIND FIX 21.ods".

The code searches for lookup 4 from cell A1. It finds the 4 in cell D23. After the last write I need the code to ClearContents or delete all information in cells E23, F23, G23, H23.

That’s it. Thank you again for looking into this. I was so exciting with the your last correction that I forgot to make sure that the clear was still there.

I was thinking it may be easier to re-post this reply with the spreadsheet. I’m not sure how easy it’s for you on your side.

The code searches for lookup 4 from cell A1. It finds the 4 in cell D23. After the last write I need the code to ClearContents or delete all information in cells E23, F23, G23, H23.

ASK FIND FIX 21.ods (15.1 KB)

That’s it. Thank you again for looking into this. I was so exciting with the your last correction that I forgot to make sure that the clear was still there.

Hi joshua4

Thanks for your suggestions.

Number 1. I use the code to search for specific numbers in a database for my own use.
Number 2. I don’t know what you’re referring to.
Number 3. I don’t know what you’re referring to.
Number 4. I don’t know what you’re referring to.

I’m very much a rookie at all this. Any help with my macro will be greatly appreciated.

Please do not use the Answer or Suggest a solution field for comments that are not an answer to the original question / solution to the problem, use Comment instead. Thanks.