How to apply 'for' loop for repetitive tasks in multiple column

[Ronith]

Hi All,

I am new to VBA coding and would be great if anyone can help me with the below two points:

1. I have written a code however not able to put it into a for loop, so that i can stop rewriting the same code for multiple columns, secondly i am not able to add piece of code to replace single backslash with double backslash in the same code, below is the code i am using:

Please Login or Register  to view this content.

2. Another question i have is, if in a particular cell i have 2 values with '.' (dot) separator i can replace it with ',' (comma) or if there are multiple spaces i can trim it to one space however, if there is 1 value in the same cell its still replacing it with ',' as per the above rule, as for single value i just want to trim with single space or remove the dot if any.

EX:

'ABC. DEF' should get replaced as 'ABC,DEF' by removing space and replace the dot'.' with comma',' (below code is working till here)
if it's only 'ABC .' its converting it as 'ABC,'
What amendment i can make here so it becomes only 'ABC' (trim the space and remove any special character) in case of single value in that cell.

Using the below code:

Please Login or Register  to view this content.

Thanks!

[TMS]

Rather than using column letters, use column numbers in your cell references. Then you can have an outer loop where you vary the column number.

Administrative Note:

Welcome to the forum.

You need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional.)

[Ronith]

Thanks for your note TMS..i have used the relevant tags now. please let me know if it isn't fine.

Is it feasible for you to amend my code on how can i use the outer loop, also regarding second point do you have any idea how can i amend this.

Many Thanks!

[TMS]

This is the code to use an Outer Loop for the columns

Please Login or Register  to view this content.

As for the second question, use InStr to check for the character.

For example:

Please Login or Register  to view this content.

[TMS]

Thanks for the rep.


I trust it has helped you.