Execute formula for range

[TomBP]

Dear community,

I managed to come up with the below code.

Please Login or Register  to view this content.

This will trim the value for a particular cell. Now I want to expand this coding to work for each cell which is not empty in column A.

What is the best way to handle this?

[Richard Buttrey]

Hi,

Try

Please Login or Register  to view this content.

[Zer0Cool]

Just curious, but why are you doing this with a macro?

You could simply put the TRIM formula in an adjacent cell, fill down and then copy/paste special values over the originals and remove the formula.

The above observation is of course based on the likely narrow scope you have given us. If your overall goal goes far beyond this, instead of asking small questions for each step along the way of coding what you are trying to do, just share the overall goal so we can give the best overall guidance and help possible. By asking as you go, the responses you get cannot factor in the other parts of your goal, so part A may not be conducive to what you need in part C, etc.

[TomBP]

Just curious, but why are you doing this with a macro?

You could simply put the TRIM formula in an adjacent cell, fill down and then copy/paste special values over the originals and remove the formula.

The above observation is of course based on the likely narrow scope you have given us. If your overall goal goes far beyond this, instead of asking small questions for each step along the way of coding what you are trying to do, just share the overall goal so we can give the best overall guidance and help possible. By asking as you go, the responses you get cannot factor in the other parts of your goal, so part A may not be conducive to what you need in part C, etc.

The goal is to TRIM multiple columns and I don't know what the row length will be in advance.

Creating adjacent columns and fill down the formula would be too much of a hassle.

I'm also not using a "simple" TRIM function but the below formula. How this needs to be translated into VBA is something I have yet to figure out.

=TRIM(CLEAN(SUBSTITUTE(A2;CHAR(160);" ")))

[Richard Buttrey]

What do you mean by 'translated into VBA?'

Do you mean you want VBA to create that formula and enter it on the sheet, or that you want the result of such a formula to appear. If the latter then my last post shows one way.

[TomBP]

What do you mean by 'translated into VBA?'

Do you mean you want VBA to create that formula and enter it on the sheet, or that you want the result of such a formula to appear. If the latter then my last post shows one way.

I mean that I want the result of such a formula to appear. You are correct that your earlier post shows a way and actually works. Kudos to you.

When I say translate to VBA, I mean the following: I was hoping that my sample case with the test data would be easily transferred to my actual formula. I thought the below coding would do the trick, but apparently it still needs some tweaking.

Please Login or Register  to view this content.

[Richard Buttrey]

It's still not clear, at least to me. Are you wanting an Excel fomula on the sheet or some VBA code?

Perhaps it would help if you uploaded an example workbook and manually add the results you expect. Be sure to explain which is original data and which are the required results,

I was also puzzled by your earier reference about having the solution applied to all cells that aren't empty. Since the essence of the task is to trim some characters from sells then I don't see why you want to try and distinguish blank cells since trimming a blank cell will simply leave the blank untouched.

[xlnitwit]

All of those functions would require a range passed to them, not an array, so you cannot nest them like that. You might use Evaluate like this

Please Login or Register  to view this content.

[TomBP]

All of those functions would require a range passed to them, not an array, so you cannot nest them like that. You might use Evaluate like this

Please Login or Register  to view this content.

That's exactly what I needed. Thx