Hi there. I have a problem with the Range.Find method. For some reason, it works fine in Excel, but not in Calc. I have a sheet with lot of notes taken, including #hashtags. I developed a VBA sub() that retrieves all the lines in my spreadsheet containing a specific hashtag. I made a simple test case for you that captures the essence of the problem. It can be downloaded here
This is the VBA code:
Rem Attribute VBA_ModuleType=VBADocumentModule Option VBASupport 1 Sub test() Dim s As String ' String to search Dim r As Range ' Matching cell With Worksheets("Sheet1") s = .Range("B1").Value Set r = .Cells.Find(What:=s, After:=Cells(2, 1), LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If not r Is Nothing then .Cells(r.Row, r.Column + 1).Value = "Found here" else .Cells(1, 3).Value = "Not found!" End With End Sub
Basically, it takes the value of the B1 cell, and looks for it in the spreadsheet. When found, it displays “Found here” next to the value.
If B1 is set to “#ai”, then it does not find the value on row 4 as expected, but on row 8! The character ‘#’ seems to have a special meaning, like it stood for a digit. See below:
It does not work either when trying to escape the ‘#’ (B1=’\#ai’); the ‘\’ is just taken literally.
So I have no way of making it work. Can someone please help?