replacement not working properly with vba?

susa23

New Member
Joined
Jan 20, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA 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. :( )
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 20, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 20, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 20, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 20, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Jan 20, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,857
Messages
5,627,278
Members
416,236
Latest member
Lynchbox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top