[Calc] If B1 contains 'peter', then....

Creating a macro - Writing a Script - Using the API

[Calc] If B1 contains 'peter', then....

Postby fpi2010 » Sat Nov 13, 2010 7:07 am

Hello experts,

Can someone help me generating a macro for me.

For column B & column C check each cell value. for example,
if B1 cell contains the word "peter" AND C1 cell contains the word "valid" then put the value "10" in A1
if B2 cell contains the word "tom" AND C2 cell contains the word "invalid" then put the value "20" in A2

basically, I am testing two columns B & C's values, and if they match my condition, then put a value in column A

a macro in libreoffice basic would be great.

Thanks a lot.

Andrew
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
fpi2010
 
Posts: 5
Joined: Wed Oct 13, 2010 9:54 am

Re: Help me with this script - if B1 contains 'peter', then.

Postby Zizi64 » Sat Nov 13, 2010 8:14 am

I think, you not needed MACRO for this task.

You have those TWO names only?
If the answer is YES, just type that FORMULA in A1 :
=IF(AND(B5="peter";C5="valid");10;IF(AND(B5="tom";C5="invalid");20;"do not match"))
and then copy-paste the formula into another row of column A.
Tibor Kovacs, Hungary; AOO4.1.1; LO4.2.8; LO4.3.7; LO4.4.3 and the latest Portable versions on Win7x64Prof, WinXPprof.SP3x86
User avatar
Zizi64
Volunteer
 
Posts: 2582
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Help me with this script - if B1 contains 'peter', then.

Postby fpi2010 » Sat Nov 13, 2010 9:10 am

Hello zizi64 again. :mrgreen:

well, the issue is that I want to check if the cell contains the word, for example,

"peter is at home" in the cell, then "peter" is contained in the cell "peter is at home"

you know what I mean?
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
fpi2010
 
Posts: 5
Joined: Wed Oct 13, 2010 9:54 am

Re: [Calc] If B1 contains 'peter', then....

Postby Zizi64 » Sat Nov 13, 2010 6:15 pm

Yes, that is possible to solve with BASIC MACRO "INSTR()" function.

from OOo HELP:
InStr Function [Runtime]
Returns the position of a string within another string.
The Instr function returns the position at which the match was found. If the string was not found, the function returns 0.
Syntax:
InStr ([Start As Long,] Text1 As String, Text2 As String[, Compare])
Code: Select all   Expand viewCollapse view
Example:
Sub ExamplePosition
Dim sInput As String
Dim iPos as Integer
sInput = "Office"
iPos = Instr(sInput,"c")
print iPos
end sub



But: you need use (for example )"true" and "false" words instead "valid" and "invalid" because the word "invalid" contains the word "valid".

And:
These words: "peter "; "peter,"; "Peter."; Petersen"; etc. occur in your text?
Maybe you need search the word "Peter[space]" for exact matching.
Last edited by Zizi64 on Sat Nov 13, 2010 7:33 pm, edited 1 time in total.
Tibor Kovacs, Hungary; AOO4.1.1; LO4.2.8; LO4.3.7; LO4.4.3 and the latest Portable versions on Win7x64Prof, WinXPprof.SP3x86
User avatar
Zizi64
Volunteer
 
Posts: 2582
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Calc] If B1 contains 'peter', then....

Postby Zizi64 » Sat Nov 13, 2010 7:30 pm

There is an idea:
Code: Select all   Expand viewCollapse view
Function MyTestCondition(A_string, A1word, A2word, B_string, B1word, B2word, CaseParStr as string) as integer

   If CaseParStr = "OFF" then CasePar = 1 else CasePar = 0

   if ((Instr(1, A_string, A1word, CasePar)<>0) and (Instr(1, B_string, B1word, CasePar)<>0)) then
      MyTestCondition = 20
   elseif    ((Instr(1, A_string, A2word, CasePar)<>0) and (Instr(1, B_string, B2word, CasePar)<>0)) then
      MyTestCondition = 10
   else
      MyTestCondition = 0
   endif
      
End function


Mytestcondition.ods
(11.19 KiB) Downloaded 293 times
Tibor Kovacs, Hungary; AOO4.1.1; LO4.2.8; LO4.3.7; LO4.4.3 and the latest Portable versions on Win7x64Prof, WinXPprof.SP3x86
User avatar
Zizi64
Volunteer
 
Posts: 2582
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Calc] If B1 contains 'peter', then....

Postby Villeroy » Sun Nov 14, 2010 4:23 pm

case sensitively:
=IF(ISNUMBER(FIND("Peter";A1));"Hi Peter";"Missing Peter")
case insensitively:
=IF(ISNUMBER(SEARCH("Peter";A1));"Hi Peter";"Missing Peter")
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, latest OpenOffice & LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 19790
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests