[Solved] Find "p" Or "q" Or "r" then write "x", "y", "z"

[Math]

Greetings ,

I'm looking for another difficult solution.

Details :

I already have a topic of mine where sr. FJCC presents a solution for finding the letter "p" in the "T" column and then writing the letter "x" viewtopic.php?f=20&t=95873

I would like to use the same macro, with the same logic, to increase the power of action.

I now want to use the same current macro to find the letter "p" or letter "q" or letter "r" in the "T" column and then write the letter "x", letter "y", letter "z" corresponding to each one, that is, if you find "p" write "x", if you find "q" write "y", find "r" and write "z".

So, I need to increase the power of Location of the macro.


hugs friends

[FJCC]

It is simple to adapt Lupp's code

Code: Select all

Sub doStrangeThings2
doc = ThisComponent
theSheet  = doc.Sheets.GetByName("Sheet1")
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
    If oneDA(i)(0)="p" Then
      oneDA(i)(0) = "x"
    ElseIf oneDA(i)(0)="q" Then
      oneDA(i)(0) = "y"
    ElseIf oneDA(i)(0)="r" Then
      oneDA(i)(0) = "z"
    Else oneDA(i)(0)=""
    End If
  Next i
  With oneRg.RangeAddress
    oneRgT = theSheet.GetCellRangeByPosition(.StartColumn - 7, .StartRow, .EndColumn - 7, .EndRow)
    oneRgT.SetDataArray(oneDA)
  End With
Next k
End Sub

[Zizi64]

I already have a topic of mine where sr. FJCC presents a solution for finding the letter "p" in the "T" column and then writing the letter "x"

You already had a macro code for substitution one letter in a specific cell range.
Why you force the usage of the macros when you can not make a minimal modification in the macro code (for substitute three letters)?

Had you read Andrew Pitonyak's free macro books? Or at least the descriptions of the StarBasic?

[RoryOF]

My instinct is always to execute such a task as three passes of the simple one-letter macro (as modified for different letters).

[Math]

greetings sr. FJCC ,

the code built by sr. Lupp is also very good.

but your code worked faster here in my big file.

so I would like to adapt your code.

Is it possible to adapt your code to this new routine ?


hugs

[Math]

sr. FJCC ,

             If I want to add one more variable to Find, it's a fourth variable to Find, but this variable is now numeric, ie Find the number "0" zero, including this variable together in the same current macro, you can also add this variable number to find zero "0" together in the current code?

If you find "0" then write "1"


Note :

add this fourth variable in code sr. Lupp and If possible, in your code too ?


Many thanks friend.

[FJCC]

As RoryOF points out above, what you want can be done by repeating the macro code for each different search target. With my original code, simply repeat the part where I search for "p" to search for "q", "r" and "0". One caution is that if you want column M to have the value or 1 when a zero is found in T, you need to set

Code: Select all

oCell.Value = 1

If you want column M to have the text "1", then set

Code: Select all

oCell.String = "1"

Please try revising the code and if you have trouble, post your version of the code and an explanation of the error encountered.

[Math]

sr. FJCC,

                correction :

                I reviewed my routines again and found that the most practical way to efficiently develop my problem is to use macro sr. Lupp, that you have adapted here in this topic.

                variable :

                then you just have to add the zero search in the "T" column together in the adapted macro and then type "1" in the "M" column.

                I'm trying to add the variable from zero to "0" in code sr. Lupp that you adapted just above, in this same topic, to find "p", "q", "r", but did not succeed to add the variable from zero "0" .

                could you please help me add together too the search for the "0" in this code that you have adapted from sr. Lupp, just above, on this same topic.



hugs friend

[Lupp]

To avoid the need of creating many individual cells we can do it with (programmed) F&R in four passes.
Though this never was stated clearly (afaik) I assumed:

-1- The "p" or "q" or "r" shall only be accepted if occurring as the sole content of the respective cells.
-2- For any cells containing something else the associated target cell shall be empty in the end.

If former content of any cells in the target range shall be preserved if not "x", "y" or "z" is to apply, the new suggested solution cannot work because it starts with a complete copy of the source range.

The Sub below (also contained in the attached demo needed about 1⁠ ⁠s for 10000 rows on my old system. For 100000 rows it was about 35⁠ ⁠s.
I suppose I haven't enough memory under the actual conditions with many applications loaded.

Code: Select all

Sub strangeAgain()
REM Using some Parts of code by FJCC, related threads.
doc0     = ThisComponent
REM Create Ranges
sSheet   = doc0.Sheets(0)
tSheet   = doc0.Sheets(0)
sRg      = sSheet.GetCellRangeByPosition(19,     0, 19,     sSheet.RangeAddress.EndRow) REM Range of complete column T of the first sheet.
tRg      = tSheet.GetCellRangeByPosition(19 + 7, 0, 19 + 7, sSheet.RangeAddress.EndRow) 
REM Get addrsses.
sAddress = sRg.RangeAddress
tAddress = tRg.GetCellByPosition(0, 0).CellAddress
REM Copy.
sSheet.CopyRange(tAddress, sAddress)
REM Create ReplaceDescriptor for target range.
replDor  = tRg.createReplaceDescriptor()
replDor.SearchRegularExpression = True
REM Remove contents different from a single letter "p" or "q" or "r".
replDor.SearchString = "((^[pqr]$)|(^.*$))" : replDor.ReplaceString = "$2" : tRg.ReplaceAll(replDor)
REM Replace "p" with "x".
replDor.SearchString = "p"                  : replDor.ReplaceString = "x"  : tRg.ReplaceAll(replDor)
REM Replace "q" with "y".
replDor.SearchString = "q"                  : replDor.ReplaceString = "y"  : tRg.ReplaceAll(replDor)
REM Replace "r" with "z".
replDor.SearchString = "r"                  : replDor.ReplaceString = "z"  : tRg.ReplaceAll(replDor)
End Sub

[Math]

sr. Lupp ,

               I tried running your new macro in the attached file, but nothing made the macro here for me .

               can you please check if it's working for you ?


                  Note :

                1) the "T" column has position 20 and the "M" column has position 13

                2) also needs the macro to find zero "0" and then write "1" in column "M"


hugs friend

[Math]

Found it ,

              I found the error and corrected it, I just changed "+ 7" to "- 7" to write in column "M".

             missing a variable:

             only need to add the search variable by "0".

             needs to be together in the macro, that is, it will find:

             "p", "q", "r" and "0"


hugs

[Math]

[Solved]

                finally I was able to also add the variable from zero "0".

               I want to thank all those who collaborated on this topic to be resolved.


many thanks friends .

[Lupp]

-1- I tested, of course, but in advance only in LibreOffice which I prefer for writing macros.
Just now I also tested the example in AOO 4.1.5 and it works mostly, but the first run of the ReplaceAll based on the RegEx "((^[pqr]$)|(^.*$))" is not performed correctly. The ReplaceString returned by the "$2" expression wrongly returns a part of the original content. It should return what was found for the first inner pair of parentheses. That's empty in the relevant cases. It's a bug of either the RegEx engine in AOO itself or of the ways it is used in AOO. I assumed the engine was the same one (by third party ICU) as is used in LibO. Probably a buggy version? I don't know remedy insofar except moving to LibO.

-2- If you apply =COLUMN(T1) you get the result 20. The value of the respective position property (.Column for the cell, .StartColumn or .EndColumn for the one-column-range) is 19 because the API uses indices starting with 0 (zero) what is a wide-spread usage in programming. I told you so earlier. For the same reason the column position of column M is 12 in the API. (I used column AA in the demo for thze target.)

-3- The thread again causes waste of time and induces discontent for all the participants. As I still cannot but assume that the macros you try to find aren't an appropriate approach: Cant you describe your needs without referring to macros? Basically worksheets are made for solutions by formulas.