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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
What if you use Czech with commas instead of semicolons?
VBA Code:
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)"
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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