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

[fpi2010]

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

[Zizi64]

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.

[fpi2010]

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?

[Zizi64]

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

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.

[Zizi64]

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

Mytestcondition.ods

(11.19 KiB) Downloaded 786 times

[Villeroy]

case sensitively:
=IF(ISNUMBER(FIND("Peter";A1));"Hi Peter";"Missing Peter")
case insensitively:
=IF(ISNUMBER(SEARCH("Peter";A1));"Hi Peter";"Missing Peter")

[buck1942]

Thanks to the one posting a solution to this Subject (5 YEARS ago ), regarding how to do an "instr/contains"...

Buck

[Villeroy]

Thanks to the one posting a solution to this Subject (5 YEARS ago ), regarding how to do an "instr/contains"...

Buck

Same solution with all commonly used spreadsheets since 30 years.

[cul]

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!

[Zizi64]

Please upload a real, ODF type sample file here.

[cul]

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.