[Solved] If B1 contains 'peter', then....
[Solved] If B1 contains 'peter', then....
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
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
Last edited by MrProgrammer on Thu Dec 10, 2020 5:17 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
Re: Help me with this script - if B1 contains 'peter', then.
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.
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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Help me with this script - if B1 contains 'peter', then.
Hello zizi64 again.
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?
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
Re: [Calc] If B1 contains 'peter', then....
Yes, that is possible to solve with BASIC MACRO "INSTR()" function.
from OOo HELP:
And:
These words: "peter "; "peter,"; "Peter."; Petersen"; etc. occur in your text?
Maybe you need search the word "Peter[space]" for exact matching.
from OOo HELP:
But: you need use (for example )"true" and "false" words instead "valid" and "invalid" because the word "invalid" contains the word "valid".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
Example: Sub ExamplePosition Dim sInput As String Dim iPos as Integer sInput = "Office" iPos = Instr(sInput,"c") print iPos end sub
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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Calc] If B1 contains 'peter', then....
There is an idea:
Code: Select all
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
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Calc] If B1 contains 'peter', then....
case sensitively:
=IF(ISNUMBER(FIND("Peter";A1));"Hi Peter";"Missing Peter")
case insensitively:
=IF(ISNUMBER(SEARCH("Peter";A1));"Hi Peter";"Missing Peter")
=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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Calc] If B1 contains 'peter', then....
Thanks to the one posting a solution to this Subject (5 YEARS ago ), regarding how to do an "instr/contains"...
Buck
Buck
openoffice 4.1 win 10
Re: [Calc] If B1 contains 'peter', then....
Same solution with all commonly used spreadsheets since 30 years.buck1942 wrote:Thanks to the one posting a solution to this Subject (5 YEARS ago ), regarding how to do an "instr/contains"...
Buck
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Calc] If B1 contains 'peter', then....
I have a follow u question to how I can expand on this...
I need:
if cell A1 DOES contain EITHER "LP", "12"", "10"", or "7"" return "Record" (yes those are record sizes so the extra " denotes inches)
if cell A1 DOES NOT contain "LP", "12"", "10"", or "7"" but DOES contain "CD" return "CD" (should also allow CDr)
If cell A1 DOES NOT contain "LP", "12"", "10"", or "7"" but DOES contain "Cass" return "Cassette"
If cell A1 DOES NOT contain "LP", "12"", "10"", "7"", or "CD" but DOES contain "DVD" return "DVD"
It all needs to fit in one cell...all help appreciated as always!
I've tried working with
=IF(ISNUMBER(SEARCH("LP";A1));"Vinyl";"Not Found") and
=IF(ISERR(SEARCH("LP",A1)),"Not found","Vinyl")
but I'm not getting very far when I try to add in other options.
Thank you!
I need:
if cell A1 DOES contain EITHER "LP", "12"", "10"", or "7"" return "Record" (yes those are record sizes so the extra " denotes inches)
if cell A1 DOES NOT contain "LP", "12"", "10"", or "7"" but DOES contain "CD" return "CD" (should also allow CDr)
If cell A1 DOES NOT contain "LP", "12"", "10"", or "7"" but DOES contain "Cass" return "Cassette"
If cell A1 DOES NOT contain "LP", "12"", "10"", "7"", or "CD" but DOES contain "DVD" return "DVD"
It all needs to fit in one cell...all help appreciated as always!
I've tried working with
=IF(ISNUMBER(SEARCH("LP";A1));"Vinyl";"Not Found") and
=IF(ISERR(SEARCH("LP",A1)),"Not found","Vinyl")
but I'm not getting very far when I try to add in other options.
Thank you!
LibreOffice on Windows 7 (sometimes Mac)
Re: [Calc] If B1 contains 'peter', then....
Please upload a real, ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Calc] If B1 contains 'peter', then....
Sorry, I thought I was in calc help. I didn't realise this was Macros. Uploading ODS anyway!
I'm trying to populate sheet 1 column K using data from sheet 2 column F.
I'm trying to populate sheet 1 column K using data from sheet 2 column F.
- Attachments
-
- Products Template.ods
- (25.14 KiB) Downloaded 170 times
LibreOffice on Windows 7 (sometimes Mac)