convert excel formula into macro

[abraham30]

Hello Everyone.

I want to convert three excel formula into macro.

1)First function, SUBSTITUTE should work on columns (H, I and J) of sheet "DATA" to remove single and multiple spaces. (e.g same formula should used like for col B of output sheet)

2)Second, user-defined function (PARSE) should work on column I and J of sheet "Data" (e.g same formula should used like for col C of output sheet)

3)Third, SUBSTITUTE should work on columns (H, I and J) of sheet "DATA" to replace semicolon with new line (e.g same formula should used like for col D of output sheet)

4)Use wrap text button so that data will be present properly.


For the demo, I put all this three formula in column B, C, D of sheet "Output" by taking a single column from old sheet.

Advance thanks for your help

[noboffinme]

Hi abraham30,

You can use the usual Functions by using the below in the IDE;

Application.WorksheetFunction.Substitute/SumIf/CountIf etc etc etc

Hth

[abraham30]

I have not that much idea about scripting.
Can you please spare few minutes from your valuable time and looking into this matter.

Advance thanks

[noboffinme]

OK, Here's how Substitute works in VBA;

This example removes the spaces in the Cell 'A1".

Please Login or Register  to view this content.

The 'PARSE' User Defined Function (UDF) is already written in VBA.

Hth