replacement not working properly with vba?

[susa23]

Hello, I have problem with vba replacement function, could someone help me please? I couldn't find solution so far..

basically I need to do CTRL+H with vba code, so I recorded it, got this code:

Columns("BS:BS").Select
    Selection.Replace What:="uvod=", Replacement:="=", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False

when I use this without vba, it works perfectly... but when I run this code, all "uvod=" part of strings just remain in the cells like nothing happened at all, no error message, so it seems like script is doing nothing.. any idea what am I doing wrong?

("uvod" can be replaced by any string, I just need to get = to the beginning of cell somehow without an apostrophe; if it is not possible to do this, secondary question would be how can I insert my concatenated formula into cell, I have Czech language version of excel, so I can't use evaluate, I have string "=když(....)" but vba is apparently not able to insert it as string without an apostrophe at the beginning. )

 

[jasonb75]

What if you use Czech with commas instead of semicolons?

Selection.Formula = "=KDYŽ(A(A(INDEX(4:4,POZVYHLEDAT(""AGE"",$3:$3,0))>=15,INDEX(4:4,POZVYHLEDAT(""AGE"",$3:$3,0))<=34),NEBO(INDEX(4:4,POZVYHLEDAT(""SEX"",$3:$3,0))=1),NEBO(NEBO(INDEX(4:4,POZVYHLEDAT(""EDUCATION"",$3:$3,0))=5),NEBO(INDEX(4:4,POZVYHLEDAT(""SOCIAL_STATUS"",$3:$3,0))=4,INDEX(4:4,POZVYHLEDAT(""SOCIAL_STATUS"",$3:$3,0))=3,INDEX(4:4,POZVYHLEDAT(""SOCIAL_STATUS"",$3:$3,0))=2))),1,0)"

 

[susa23]

I'm wondering if entering the formula in a different format will work without the need for find and repalce.

With the English translation of the formula, I can enter it into a cell using vba without problem. Formula length is 312 characters so it is past the current problem limit ?

I don't know if excel will translate if you try to enter the formula in english, a quick search online suggests that it is the way to do it but there appears to be conflicting information in some places. The following line of code will attempt to enter the english version of the formula into the selected cell.

Selection.Formula = "=IF(AND(AND(INDEX(4:4,MATCH(""AGE"",$3:$3,0))>=15,INDEX(4:4,MATCH(""AGE"",$3:$3,0))<=34),OR(INDEX(4:4,MATCH(""SEX"",$3:$3,0))=1),OR(OR(INDEX(4:4,MATCH(""EDUCATION"",$3:$3,0))=5),OR(INDEX(4:4,MATCH(""SOCIAL_STATUS"",$3:$3,0))=4,INDEX(4:4,MATCH(""SOCIAL_STATUS"",$3:$3,0))=3,INDEX(4:4,MATCH(""SOCIAL_STATUS"",$3:$3,0))=2))),1,0)"

One article that I looked at suggested using FormulaLocal instead of Formula, although I'm not familiar with the difference.

btw your code works in my excel, but when i have exactly same string saved in variable (tried just replacing keywords from czech to english and ; with ,) then it doesn't work (application defined or object defined error), interesting :D nevermind, let's do it completely other way...

 

[jasonb75]

I only got object defined error when I had ; in the formula instead of ,

I think to say that it is confusing would be an understatement.