Combining 2 or more Formulae in VBA

[shoogerjar]

Hi,

I need help in translating the below formula into vba code. Thank you.

=IF(ISERROR(FIND("LK",A47)),"false","true")

 

[Michael M]

Try, for example

Range("A1").Formula = "=IF(ISERROR(FIND(""LK"",A47)),""false"",""true"")"

 

[vds1]

Assuming this goes to Cell B47 in Sheet1 then

sheets("[COLOR=#0000cd]Sheet1[/COLOR]").Range("[COLOR=#0000cd]B47[/COLOR]").formula="=IF(ISERROR(FIND(""LK"",A47)),""FALSE"",""TRUE"")"

Change Accordingly.

 

[shoogerjar]

Thanks both for your prompt help!

 

[shoogerjar]

Hi,

I tried to incorporate 2 searches into one formula but ended up with the "application defined object defined error". The code is as follows:

[COLOR=#333333]Range("A1")[/COLOR].Formula = "=OR(IF(ISERROR(FIND(""LK"",U1)),FALSE,TRUE),IF(ISERROR(FIND(""SK"",U1)),FALSE,TRUE)"

I am trying to identify if the cell in U1 contains either LK or SK. Appreciate if anyone can help with this. Thank you.

 

[Michael M]

Maybe this instead

Range("A1").Formula = "=IF(OR(U1=""LK"",U1=""SK""),TRUE,FALSE)"

 

[shoogerjar]

Hi Michael,

Thanks for your reply but I am actually looking at a formula to check if the cell contains either LK or SK (For e.g. BULK or DUSK) hence your formula will not apply for this case.

 

[Michael M]

Sorry, didn't realise that.
Try

Range("A1").Formula ="=IF(OR(ISERROR(FIND(""LK"",U1)),ISERROR(FIND(""SK"",U1))),FALSE,TRUE)"

But do you understand that this will always return FALSE, unless there is something like "SKULK" in U1

 

[shoogerjar]

Hi Michael, yes I tried your formula and it will always return FALSE. Hence I used

Range("A1").Formula = "=OR(IF(ISERROR(FIND(""LK"",U1)),FALSE,TRUE),IF(ISERROR(FIND(""SK"",U1)),FALSE,TRUE)"

with the OR outside instead of the IF. It works when I enter it in a normal excel cell but gives me error when I try to run it in vba.

 

[Michael M]

You were missing a bracket

Range("A1").Formula = "=OR(IF(ISERROR(FIND(""LK"",U1)),FALSE,TRUE),IF(ISERROR(FIND(""SK"",U1)),FALSE,TRUE))"

]