SOLVED

Calling on EXCEL EXPERTS: Simple Macro Produces Varied Results

%3CLINGO-SUB%20id%3D%22lingo-sub-2326414%22%20slang%3D%22en-US%22%3ECalling%20on%20EXCEL%20EXPERTS%3A%20Simple%20Macro%20Produces%20Varied%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2326414%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYesterday%2C%20I%20posted%20a%20discussion%20asking%20for%20help%20regarding%20a%20find%20and%20replace%20function%20I%20made%20in%20VBA.%20(That%20discussion%20can%20be%20found%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fplease-help-engineers%2Fm-p%2F2324107%23M98745%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fplease-help-engineers%2Fm-p%2F2324107%23M98745%3C%2FA%3E).%20What%20I%20discovered%20from%20my%20conversation%20with%20a%20couple%20of%20users%20is%20that%20one%20individual%20running%202016%20had%20success%20with%20this%20function%20proving%20my%20theory%20that%20something%20Microsoft%2FExcel%20changed%20is%20causing%20this%20error.%20(Thank%20you%26nbsp%3B%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20and%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EToday%20I%20am%20asking%20for%20help%20from%20the%20community%20in%20running%20the%20attached%20file%20to%20see%20what%20results%20you%20produce.%20The%20goal%20of%20the%20function%20is%20to%20find%20and%20replace%20%3A%20%2F%20%5C%20%3F%20*%20%5B%20%5D%20AND%20ALSO%20if%20the%20cell%20is%20blank%2Fnull%20fill%20it%20in%20with%20the%20word%20%22BLANK.%22%20This%20function%20should%20only%20do%20this%20on%20column%20B%20on%20the%20sheet%20%22Test%201.%22%20Unfortunately%2C%20when%20I%20run%20this%20on%20my%20computer%20the%20function%20does%20the%20find%20and%20replace%20described%20above%20for%20columns%20A%3AE%20on%20%22Test%201%22%20and%20Columns%20A%3AE%20on%20%22Test%202.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlthough%20everyone%20has%20a%20different%20way%20of%20writing%20code...syntax...methods...etc.%20I%20know%20that%20this%20code%20SHOULD%20WORK%20as%20is.%20I%20appreciate%20any%20suggestions%20to%20change%20the%20code%20but%20am%20more%20curious%20in%20your%20results%20when%20running%20this%20code%20AS%20IS%20on%20your%20computer.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20all%20don't%20mind%20just%20responding%20to%20this%20post%20with%20the%20version%20of%20excel%20you%20are%20running%20and%20also%20what%20results%20you%20achieved%20that%20would%20be%20awesome!%20(maybe%20a%20screenshot%20of%20the%20data%20in%20%22Test%201%22%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20record%2C%20I%20am%20running%20Version%2016.48%20(21041102)%20and%20the%20license%20is%20a%20Microsoft%20365%20Subscription.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2326414%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2326558%22%20slang%3D%22en-US%22%3ERe%3A%20Calling%20on%20EXCEL%20EXPERTS%3A%20Simple%20Macro%20Produces%20Varied%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2326558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1045126%22%20target%3D%22_blank%22%3E%40brianrichard1120%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20using%20Excel%202019%20(non-Microsoft%20365)%2C%20version%202104%20on%20Windows%2010%20Home.%3C%2FP%3E%0A%3CP%3EWhen%20I%20run%20the%20macro%20in%20your%20workbook%2C%20it%20only%20affects%20column%20B%3A%20empty%20cells%20are%20filled%20with%20BLANK%2C%20and%20special%20characters%20are%20replaced%20with%20spaces.%20Other%20columns%20are%20unchanged%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0373.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F278452i4ED3F4BD55AA4176%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0373.png%22%20alt%3D%22S0373.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2326581%22%20slang%3D%22en-US%22%3ERe%3A%20Calling%20on%20EXCEL%20EXPERTS%3A%20Simple%20Macro%20Produces%20Varied%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2326581%22%20slang%3D%22en-US%22%3EThank%20you%20Hans!%3C%2FLINGO-BODY%3E
Occasional Contributor

Good morning,

 

Yesterday, I posted a discussion asking for help regarding a find and replace function I made in VBA. (That discussion can be found here https://techcommunity.microsoft.com/t5/excel/please-help-engineers/m-p/2324107#M98745). What I discovered from my conversation with a couple of users is that one individual running 2016 had success with this function proving my theory that something Microsoft/Excel changed is causing this error. (Thank you @Hans Vogelaar and @JMB17)

 

Today I am asking for help from the community in running the attached file to see what results you produce. The goal of the function is to find and replace : / \ ? * [ ] AND ALSO if the cell is blank/null fill it in with the word "BLANK." This function should only do this on column B on the sheet "Test 1." Unfortunately, when I run this on my computer the function does the find and replace described above for columns A:E on "Test 1" and Columns A:E on "Test 2."

 

Although everyone has a different way of writing code...syntax...methods...etc. I know that this code SHOULD WORK as is. I appreciate any suggestions to change the code but am more curious in your results when running this code AS IS on your computer. 

 

If you all don't mind just responding to this post with the version of excel you are running and also what results you achieved that would be awesome! (maybe a screenshot of the data in "Test 1" sheet.

 

For the record, I am running Version 16.48 (21041102) and the license is a Microsoft 365 Subscription.

 

 

 

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@brianrichard1120 

I'm using Excel 2019 (non-Microsoft 365), version 2104 on Windows 10 Home.

When I run the macro in your workbook, it only affects column B: empty cells are filled with BLANK, and special characters are replaced with spaces. Other columns are unchanged:

 

S0373.png

For the record, I am running Version 16.48 (21041102) and the license is a Microsoft 365 Subscription.

Both my test 1 sheet and test 2 sheet produce this result I have attached.