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]

If it appears that nothing is happening then it means that the resulting formula would be invalid in some way.

Wrong argument types, missing parenthesis or invalid table names are a few possible reasons that come to mind.

 

[susa23]

If it appears that nothing is happening then it means that the resulting formula would be invalid in some way.

Wrong argument types, missing parenthesis or invalid table names are a few possible reasons that come to mind.

how is it possible when I am running only the code I recorded on exactly same data, is it recorded wrong then?

 

[jasonb75]

Possibly. Without seeing the original and the result, I can't say for certain. I do recall seeing something similar before where there were problems with vba recognising certain european language characters.

You mention that it was started from a string so the error is more likely to be there than in the find and replace part.

 

[susa23]

Possibly. Without seeing the original and the result, I can't say for certain. I do recall seeing something similar before where there were problems with vba recognising certain european language characters.

You mention that it was started from a string so the error is more likely to be there than in the find and replace part.

ok, I tested it with another strings and problem is probably within the length of the string... when I create new file, write only short strings like "abcd", "bcdef" and so on and replace "bcd" with "=" then it works normally, but when i go like "bcd..." followed by 300 another chars, then problem is here... so is there any length limit for this function or any way how to go around it?

 

[susa23]

ok, I tested it with another strings and problem is probably within the length of the string... when I create new file, write only short strings like "abcd", "bcdef" and so on and replace "bcd" with "=" then it works normally, but when i go like "bcd..." followed by 300 another chars, then problem is here... so is there any length limit for this function or any way how to go around it?

more testing - it is not possible to have string beginning with = and then more than 255 chars... so problem isn't only length but combination of length and equal char at beginning

 

[jasonb75]

is there any length limit for this function or any way how to go around it?

I believe that the 255 character limit that you have encountered applies to all text functions.

Sometimes workarounds can be found, but we would need to see an example of the full string that you're trying to enter in order to establish possibility and method.

 

[susa23]

I believe that the 255 character limit that you have encountered applies to all text functions.

Sometimes workarounds can be found, but we would need to see an example of the full string that you're trying to enter in order to establish possibility and method.

no problem, the example of full string is here:
abc=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)
(there are formulas from czech version of excel, not sure if it helps, but english replacements should be KDYŽ=IF, POZVYHLEDAT=MATCH, A=AND, NEBO=OR)
trying to remove "abc" here, with ctrl+H it's working, vba replace not... formula alone is working as well..

 

[jasonb75]

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.

 

[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.

yea, this works if i use english commands, czech equivalents aren't working with .formula... I will go around it, translate or use another method, I just don't understand, why CTRL+H is working and vba replace is not in this case nevermind i guess, thank you for replying