Parsing text with VBA

I'm looking to write a macro that can take text from one cell and set a user defined number of spaces between it along with the text from another cell. I did it manually and used the macro recorder, but it doesn't really help
ie

Please Login or Register  to view this content.

As I want to be able to tell the macro how many spaces I want between the text.
Eventually I will write the parsed text to a text file.

Is there a way to do this with VBA? Can someone just point me in the right direction?

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

hi Carnifex,

Yes, VBA can do this.
Look up "WorksheetFunction.Rept Method" in the VBE Help files, and create a variable that is populated by an Input box to provide the second argument to the REPT method.

btw, your existing code can be shortened to the below by removing the first ".select":

Please Login or Register  to view this content.

hth
Rob

Look up WorksheetFunction.Rept

Or see VBE Help for the native String function.

Hello Carnifex930,

Welcome to the Forum!

I think this what you are after. This macro is a User Defined Function. It works similarly to a worksheet function. It will create the new text using the cells and the number of spaces you provide. Copy this code into a VBA module.

NOTE: Changing the values of either cell will not change the UDF result. If you need this to happen, let me know.

Please Login or Register  to view this content.

UDF Example

Please Login or Register  to view this content.

Adding the Macro

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Thanks for all the help. Maybe I can get just a little more advice.

The function that Leith posted:

Please Login or Register  to view this content.

Does the trick. However, I'm looking to do this on a larger scale; for now I would like to assume just one row of data (text in each cell).

Say we have 4 cells. Cell1, Cell2, Cell3, Cell4

The program should be able to define a string variable,and store Cell1's text in it.
Next a function will be used to take Cell2's text and the string variable(has cell1's text in it), which it will then parse it a number of (known) spaces and then store back into the string variable, the moves on to Cell3 take its text and parses it a number of spaces with the string variable (String Variable now has Cell1's text parsed corecctly with Cell2) and the stores it back into the String Variable.

The end result is a string variable that Contains the text from Cell's 1,2,3 and 4 parsed correctly.

Sorry I'm new to VBA, I can figure out all the loopin, I just need to adapt the above function to save into a string variable, not a cell, How do I do this?

hi Carnifex930

You may have already solved this, but just in case you haven't...
To use the function that Leith posted you can call it from another macro to define a string variable by putting it on the right of the equals sign in your code, for example:

Please Login or Register  to view this content.

hth
Rob