[Solved] Find the letter "p" and write the letter "x"

[Math]

I need a help with Macro that worked perfect in Excel Vba.

                    I need to turn this Macro into LibreOffice BASIC code.

                    the vba macro in excel did the following:

                    Look for the letter "p" in the "T" column of the worksheet ("2.2Ped Fat"), when you find the letter "p" in the T column, move the cursor 7 positions to the left and write the letter "x".

                    you need to do the search in the T (Loop) column until you can not find any "p" letters.

                    below is the vba code that I used in excel:

========================================================================================
Sub Find_x()
Dim localizador As Range
Worksheets("2.2Ped Fat").Activate
Range("M50:M20000").ClearContents
With Plan1.Range("T:T")
Set localizador = .Find("p", LookIn:=xlValues, LookAt:=xlWhole) 'procura exata
If localizador = 0 Then Exit Sub
If Not localizador Is Nothing Then
ENDERECO = localizador.Address
Do
Range(localizador.Address).Select
Range(localizador.Address).Offset(0, -7) = "x"
Set localizador = .FindNext(localizador)
Loop While Not localizador Is Nothing And localizador.Address <> ENDERECO
End If
End With
End Sub
===================================================================================


hugs.

[Villeroy]

Replace OpenOffice with LibreOffice and see if LibreOffice can handle this bullshit.

Just out of curiosity:
Why do you call
Range(localizador.Address).Select
instead of
localizador.Select

[Math]

sr. Villeroy ,

               I used LibreOffice to run the vba macro as you guided me

               An Error Has Occurred in the Following Command Line >

               Set locator = .Find ("p", LookIn: = xlValues, LookAt: = xlWhole)

               I could not understand why ????


               referring to your question >

               is to refer to the entire T column, which I use in the vba: Range (locator.Address) .Select

               that is >

               Range > runs through the T column


thanks for the help

[FJCC]

Here is an OpenOffice Basic version of your request.

Code: Select all

  oSheets = ThisComponent.getSheets()
  oSheet1 = oSheets.getByName("Sheet1")
  oColumns = oSheet1.getColumns()
  
  oT = oColumns.getByName("T")
  oSDesc = oT.createSearchDescriptor()
  oSDesc.setSearchString("p")
  oFound = oT.findAll(oSDesc)
  if Not IsNull(oFound) Then
    for i = 0 to oFound.Count - 1
      oRng = oFound.getByIndex(i)
      RngAddr =  oRng.RangeAddress
      for Row = RngAddr.StartRow to RngAddr.EndRow
        oCell = oSheet1.getCellByPosition(12, Row)
   	    oCell.String = "x"
      next Row
    next i
  End If

[Math]

sr. FJCC,

        I will test your code, when I have the results, I will inform you.


Thanks a lot for the help

[Villeroy]

Please do us all a favour and keep on using Excel.

[RoryOF]

I agree with Villeroy: if there is a program that does a task _YOU_ cannot migrate to Calc, keep on using that program. It is unfair to expect the Users of OO and the Forum volunteers to make up for your lack of ability.

[Lupp]

(Just for fun, not for actual use.)
@FJCC:

In case of a dense p-population in column T this should be more efficient:

Code: Select all

Sub doStrangeThings
doc = ThisComponent
theSheet  = doc.Sheets.GetByName("mySheetName")
theRgS    = theSheet.GetCellRangeByName("T1:T1048576")
theCrgs   = theRgS.QueryContentCells(20) REM Replace this 20 by 4 if formula results are not accepted.
uk = theCrgs.Count - 1
For k = 0 To uk
  oneRg = theCrgs(k)
  oneDA = oneRg.GetDataArray
  ui = Ubound(oneDA)
  For i = 0 To ui
    oneDA(i)(0) = IIf(oneDA(i)(0)="p","x", "") REM Adapt the condition to the needs if not comparison with the complete cell content is intended.
  Next i
  With oneRg.RangeAddress
    oneRgT = theSheet.GetCellRangeByPosition(.StartColumn - 7, .StartRow, .EndColumn - 7, .EndRow)
    oneRgT.SetDataArray(oneDA)
  End With
Next k
End Sub

[Math]

sr. FJCC and sr. Lupp ,

Your contribution was very good

macro conversion was a success

Thank you so much for your help


hugs