Need to replace formulas with code

[RAJESH1960]

Hello guys,

With the help of a code, I have this result data in sheet B from column A to G in a vertical order. Columns K:BD contain formulas to sort the data horizontally as shown in the image. As the formulas are too lengthy and in thousands of cells, the code takes a lot of time in calculating threads. To reduce the time taken for the macro to get the result, I was hoping somebody willing to help me to write a code to get the result from column A to G to Columns K:BD.

Shared Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD
1											COPY THE RESULT AND PASTE TO NEW SHEET WITH PASTE SPECIAL - VALUES
2	Date	Vch Type	Vch No.	Narration	Particulars	Debit Negative	Credit Positive		Total Amt		Date	Vch Type	Vch No.	Narration	Ledger 1	Amt	Ledger 2	Amt	Ledger 3	Amt	Ledger 4	Amt	Ledger 5	Amt	Ledger 6	Amt	Ledger 7	Amt	Ledger 8	Amt	Ledger 9	Amt	Ledger 10	Amt	Ledger 11	Amt	Ledger 12	Amt	Ledger 13	Amt	Ledger 14	Amt	Ledger 15	Amt	Ledger 16	Amt	Ledger 17	Amt	Ledger 18	Amt	Ledger 19	Amt	Ledger 20	Amt	Ledger 21	Amt
3	02-08-2021	Receipt	1026		ICICI	-16380.00			-16380		02-08-2021	Receipt	1026		ICICI	-16380	January	4823	February	11720	March	-163
4	02-08-2021	Receipt	1026		January		4823.00		4823		03-08-2021	Receipt	1027		ICICI	-2000	January	1000	January	1000
5	02-08-2021	Receipt	1026		February		11720.00		11720		03-08-2021	Receipt	1028		ICICI	-2770	January	2800	February	-30
6	02-08-2021	Receipt	1026		March	-163.00			-163		04-08-2021	Payment	1029		ICICI	1062	Sunday	-944	Monday	-118
7	03-08-2021	Receipt	1027		ICICI	-2000.00			-2000		04-08-2021	Receipt	1030		ICICI	-1704	Monday	984	Tuesday	720
8	03-08-2021	Receipt	1027		January		1000.00		1000		04-08-2021	Payment	1031		ICICI	94572	Monday	-94612	Tuesday	40
9	03-08-2021	Receipt	1027		January		1000.00		1000				0			0
10	03-08-2021	Receipt	1028		ICICI	-2770.00			-2770							0
11	03-08-2021	Receipt	1028		January		2800.00		2800
12	03-08-2021	Receipt	1028		February	-30.00			-30
13	04-08-2021	Payment	1029		ICICI		1062.00		1062
14	04-08-2021	Payment	1029		Sunday	-944.00			-944
15	04-08-2021	Payment	1029		Monday	-118.00			-118
16	04-08-2021	Receipt	1030		ICICI	-1704.00			-1704
17	04-08-2021	Receipt	1030		Monday		984.00		984
18	04-08-2021	Receipt	1030		Tuesday		720.00		720
19	04-08-2021	Payment	1031		ICICI		94572.00		94572
20	04-08-2021	Payment	1031		Monday	-94612.00			-94612
21	04-08-2021	Payment	1031		Tuesday		40.00		40
B

 

[RAJESH1960]

Checking with other larger data tonlight.

 

[johnnyL]

Did you try rebooting computer?

 

[RAJESH1960]

Did you try rebooting computer?

What is rebooting.? Never done before anything like that.

 

[johnnyL]

Turn computer off and then back on again.

 

[RAJESH1960]

Turn computer off and then back on again.

Hahahaha. I do that everyday. JohnnyL Is the sheet Formulas connected to the code because I can't find any connection anywhere. What if I delete the sheet?

 

[johnnyL]

The formulas for the K:BD range as well as the Column I formula are not used from the Formulas sheet. You have a couple other formulas on the Formulas sheet, but I don't think they are used. So the short answer is: the code I provided does not use the Formulas sheet.

 

[RAJESH1960]

The formulas for the K:BD range as well as the Column I formula are not used from the Formulas sheet. You have a couple other formulas on the Formulas sheet, but I don't think they are used. So the short answer is: the code I provided does not use the Formulas sheet.

It was just workings except for the two formulas. I will delete the sheet. Tonight I am going to complete this project which will be the last step adding a few more codes to the sheet to generate xml. And also hiding all the sheets except original. It is going to be a long night. Hope I don't face any problems. ?.

 

[johnnyL]

Good Luck!

 

[RAJESH1960]

Good Luck!

Just awesome. As the data was around 1200 rows, it took Generate XML 30 seconds and ClearData in just 4 seconds. Just like I dreamed off. JohnnyL, your effort has paid off. People like you inspire me to think of the impossible and try to make it possible. Hats of to you man. ?

 

[johnnyL]

Post your code!

Also, was your run time error 7, that you mentioned earlier, solved with the reboot of the computer?

On a side note, Earlier, I was going to say 'rebooting' the computer is when you are so frustrated that you continually kick the computer until the error message on the screen goes away.