Is there a VBA method to modify many columns of data rather than by inserting new columns with formulas?

[ExcelLearner25]

I have a big spreadsheet that needs lots of processing to fix problems introduced from a conversion. I've been using VBA to mimic how I would do it manually: insert blank columns, put a formula in each column to modify the old data, then hide or delete the original column, leaving the new columns with the modified data (I convert it to values too when it's done).

But adding all these columns and formulas with code seems like an inefficient way of doing this. Is there a better way, such as by having VBA that directly modifies the original columns of values?

 

[MARK858]

having VBA that directly modifies the original columns of values?

What exactly is wrong with the original values (post a small sample using the XL2BB addin please)

 

[ExcelLearner25]

It's a lot of formatting problems. For example, the conversion sometimes inserts the word "Effective" into the date column rather than the "Description" column where it belongs. So I have some code that strips "effective" out of the date column, and properly formats the remaining text as a date (deleting line returns, blank spaces), and separate code that adds the word to the proper location in the description. All this includes IF statements to make sure it's only done where necessary.

Elsewhere, a column of dollar values is in text format with negative values using a trailing hyphen. So I fix those too.

There are lots of other little fixes like this. (I don't have time at this minute to learn how to use XL2BB but will this weekend)

 

[MARK858]

There are lots of other little fixes like this. (I don't have time at this minute to learn how to use XL2BB but will this weekend)

I'll try and have a look once you have posted the sample as there isn't anything I can propose without seeing what we are dealing with.

 

[ExcelLearner25]

Here's a sample of what I have. The cells with orange are the starting point. The cells in red text are the finished product. I use VBA to insert all the formulas. It's not pretty, and it's difficult to maintain.

TEMP.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	0296 S/F WU	0296	S/F WU	S/F WU	S/F WU	PAYMENT Int pd to: REF#	PAYMENT	Int pd to: REF#	Int pd to: REF#	Int pd to:	REF#	REF#	S/F WU REF#	Int pd to: 4/03/22 S/F WU REF#
Sheet1

Cell Formulas
Range	Formula
D4,L4,I4,G4	D4	=RegSplit(C4, "[\r\n].",0," (CaseInSensitive)")
E4,M4,J4	E4	=SUBSTITUTE(C4,D4,)
F4,N4,K4	F4	=SUBSTITUTE(E4,CHAR(10),"",1)
O4	O4	=G4 & " " & N4
P4	P4	=TRIM(L4 & " " & T4 & CHAR(10) & O4)