Is there an easy way to add formulaR1C1 into VBA arrays to speed up calculation time?

[ianawwalker]

Hello,

I am putting in a bunch of formulas into 10 columns, that goes down 12,000 times or more. Which is causing the VBA to slow down exponentially. I do not have much experience with arrays and was thinking there should be a way to add my worksheets into different arrays, put the calculations into one of the arrays and then paste the data into my final worksheet that i am using to update a different worksheet. For instance I have data that I am combining from US_FileInput and US_Exceptions that goes into US_Combine. The US Combine takes a bunch of time to run the calculations, because of the formulas i am using to translate the data from a vendor to my companies reporting. The VBA works to translate this data, but takes anywhere from 15-30 min to run. Is there a way to put all of the worksheets into arrays, run the calculations in arrays, and then have the data spit back out into my final worksheet of US_Combine?

Sub UScombine_moveUSdatatoUScombine()

'Application.ScreenUpdating = False
'Call TurnoffFunctionality

Sheets("US_FileInput").Range("A:A").TextToColumns
Sheets("US_Exceptions").Range("A:A").TextToColumns

'copy loan numbers & info to db_combine tab
'loan number & 'alt loan & borrower & address
Worksheets("US_FileInput").Range("A2:A25000").Copy
Worksheets("US_Combine").Range("A2").PasteSpecial xlPasteValues
Worksheets("US_FileInput").Range("H2:H25000").Copy
Worksheets("US_Combine").Range("B2").PasteSpecial xlPasteValues
Worksheets("US_FileInput").Range("I2:I25000").Copy
Worksheets("US_Combine").Range("G2").PasteSpecial xlPasteValues
Worksheets("US_FileInput").Range("M2:M25000").Copy
Worksheets("US_Combine").Range("H2").PasteSpecial xlPasteValues

'Application.ScreenUpdating = True


'create exception code for Y/N conversion
With Sheets("US_Exceptions")

.Range("B2:B25000").ClearContents
.Range("B2:B" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=RC[17]&RC[19]"
'worksheets("US_Exceptions").Range("B2:B" & Cells(Rows.Count, 3).End(xlUp).Row).Value = Range("B2:B" & Cells(Rows.Count, 3).End(xlUp).Row).Value

End With

'calculation from Tim on Y/N conversion, input info here and paste into DB_Combine
With Sheets("US_Combine")
.Range("C2:C" & .Cells(Rows.Count, 1).End(xlUp).Row).Formula2R1C1 = _
        "=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"
.Range("D2:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Formula2R1C1 = _
        "=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"
.Range("E2:E" & .Cells(Rows.Count, 1).End(xlUp).Row).Formula2R1C1 = _
        "=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"
.Range("F2:F" & .Cells(Rows.Count, 1).End(xlUp).Row).Formula2R1C1 = _
        "=SUM(COUNTIFS(US_Exceptions!C1,US_Combine!RC1,US_Exceptions!C2,INDEX(US_Codes!R2C[-1]:R11C[-1],0)))"

'.Range("C2:F" & Cells(Rows.Count, 1).End(xlUp).Row).Value = Range("C2:F" & Cells(Rows.Count, 1).End(xlUp).Row).Value
End With

'comparing records tab to exception report & match formula to records tab & converting 1/0's to Y/N's to copy/paste to records tab
With Sheets("US_Combine")
.Range("I2:I" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-8],Records!C[-8]:C[31],34,FALSE)"
.Range("J2:J" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-9],Records!C[-9]:C[30],29,FALSE)"
.Range("K2:K" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-10],Records!C[-10]:C[29],39,FALSE)"
.Range("L2:L" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-11],Records!C[-11]:C[29],39,FALSE)"
.Range("M2:M" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=MATCH(RC[4],RC[-4],0)"
.Range("N2:N" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=MATCH(RC[4],RC[-4],0)"
.Range("O2:O" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=MATCH(RC[4],RC[-4],0)"
.Range("P2:P" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=MATCH(RC[4],RC[-4],0)"
.Range("U2:U" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=MATCH(RC[-20],Records!C[-20],0)"
.Range("Q2:Q" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=IF(RC[-14]>=1,""Y"",""N"")"
.Range("R2:R" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=IF(RC[-14]>=1,""Y"",""N"")"
.Range("S2:S" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=IF(RC[-14]>=1,""Y"",""N"")"
.Range("T2:T" & Cells(Rows.Count, 3).End(xlUp).Row).FormulaR1C1 = "=IF(RC[-14]>=1,""Y"",""N"")"
'.Range("I2:U" & .Cells(Rows.Count, 1).End(xlUp).Row).Value = Range("I2:U" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With


End Sub

 

[DanteAmor]

Hi @ianawwalker and thanks for posting on the MrExcel forum.

I understand that this situation is difficult and could be affecting your work, let's work together as a team to find the best solution to this process.

In order for me to better understand the problem, I would like to request a file with data in all sheets and with the expected results. Please, include in your examples 10 to 15 records or the minimum data necessary to understand the operation, on each sheet to know how each formula works.
You could upload a copy of your file to a free site such google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

I hope to hear from you soon.
Respectfully
Dante Amor

 

[ianawwalker]

Hi Dante,

I don't have access to be able to send it via google sheets and it won't let me attach it to the chat here. any suggestions on getting that to you?

thankyou,

ian

 

[DanteAmor]

You can add here a representative sample (15 to 20 records) from each sheet (5 sheets), use the XL2BB tool for that.
NOTE XL2BB:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I tried to create the samples of each sheet to see how the sheets work.
I already understood more or less what each formula does, but I need more real or significant data to carry out tests and start creating the macro.

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A	B	C	D	E	F	G	H	I	J	K	L	M
2	A2	B2	C2	D2	E2	F2	G2	H2	I2	J2	K2	L2	M2
3	A2	B3	C3	D3	E3	F3	G3	H3	I3	J3	K3	L3	M3
4	A2	B4	C4	D4	E4	F4	G4	H4	I4	J4	K4	L4	M4
5	A2	B5	C5	D5	E5	F5	G5	H5	I5	J5	K5	L5	M5
6	A6	B6	C6	D6	E6	F6	G6	H6	I6	J6	K6	L6	M6
7	A6	B7	C7	D7	E7	F7	G7	H7	I7	J7	K7	L7	M7
8	A6	B8	C8	D8	E8	F8	G8	H8	I8	J8	K8	L8	M8
9	A9	B9	C9	D9	E9	F9	G9	H9	I9	J9	K9	L9	M9
US_FileInput

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	A2	S2U2	C2	D2	E2	F2	G2	H2	I2	J2	K2	L2	M2	N2	O2	P2	Q2	R2	S2	T2	U2
3	A2	S3U3	C3	D3	E3	F3	G3	H3	I3	J3	K3	L3	M3	N3	O3	P3	Q3	R3	S3	T3	U3
4	A2	S4U4	C4	D4	E4	F4	G4	H4	I4	J4	K4	L4	M4	N4	O4	P4	Q4	R4	S4	T4	U4
5	A2	S5U5	C5	D5	E5	F5	G5	H5	I5	J5	K5	L5	M5	N5	O5	P5	Q5	R5	S5	T5	U5
6	A6	S6U6	C6	D6	E6	F6	G6	H6	I6	J6	K6	L6	M6	N6	O6	P6	Q6	R6	S6	T6	U6
7	A6	S7U7	C7	D7	E7	F7	G7	H7	I7	J7	K7	L7	M7	N7	O7	P7	Q7	R7	S7	T7	U7
8	A6	S8U8	C8	D8	E8	F8	G8	H8	I8	J8	K8	L8	M8	N8	O8	P8	Q8	R8	S8	T8	U8
9	A9	S9U9	C9	D9	E9	F9	G9	H9	I9	J9	K9	L9	M9	N9	O9	P9	Q9	R9	S9	T9	U9
US_Exceptions

Cell Formulas
Range	Formula
B2:B9	B2	=S2&U2

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	A2	H2	1	0	0	0	I2	M2	esto q	0	0	0	#N/A	#N/A	#N/A	#N/A	Y	N	N	N	2
3	A3	H3	0	0	0	0	I3	M3	es3	0	0	0	#N/A	#N/A	#N/A	#N/A	N	N	N	N	3
4	A4	H4	0	0	0	0	I4	M4	es4	0	0	0	#N/A	#N/A	#N/A	#N/A	N	N	N	N	4
5	A5	H5	0	0	0	0	I5	M5	es5	0	0	0	#N/A	#N/A	#N/A	#N/A	N	N	N	N	5
6	A6	H6	0	0	0	0	I6	M6	es6	0	0	0	#N/A	#N/A	#N/A	#N/A	N	N	N	N	6
7	A7	H7	0	0	0	0	I7	M7	es7	0	0	0	#N/A	#N/A	#N/A	#N/A	N	N	N	N	7
8	A8	H8	0	0	0	0	I8	M8	es8	0	0	0	#N/A	#N/A	#N/A	#N/A	N	N	N	N	8
9	A9	H9	0	0	0	0	I9	M9	es9	0	0	0	#N/A	#N/A	#N/A	#N/A	N	N	N	N	9
US_Combine

Cell Formulas
Range	Formula
C2:F9	C2	=SUM(COUNTIFS(US_Exceptions!$A:$A,US_Combine!$A2,US_Exceptions!$B:$B,INDEX(US_Codes!B$2:B$11,0)))
I2:I9	I2	=VLOOKUP(A2,Records!A:AN,34,FALSE)
J2:J9	J2	=VLOOKUP(A2,Records!A:AN,29,FALSE)
K2:K9	K2	=VLOOKUP(A2,Records!A:AN,39,FALSE)
L2:L9	L2	=VLOOKUP(A2,Records!A:AO,39,FALSE)
M2:P9	M2	=MATCH(Q2,I2,0)
Q2:T9	Q2	=IF(C2>=1,"Y","N")
U2:U9	U2	=MATCH(A2,Records!A:A,0)

Dante Amor
	A	B	C
1
2		S2U2
3		S2U3
4		S2U4
5		S2U5
6
US_Codes

Dante Amor
	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
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35
2	a2																																	esto q
3	A3																																	es3
4	A4																																	es4
5	A5																																	es5
6	A6																																	es6
7	A7																																	es7
8	A8																																	es8
9	A9																																	es9
Records

Your examples are important, otherwise I would be guessing how your data is and I would not reach a definitive result and I would be modifying the macro many times and you would be testing a macro many times that would not reach the final goal.

----- --
I hope to hear from you soon.
Respectfully
Dante Amor

 

[ianawwalker]

Hi Dante,

I hope this helps, but i'm not able to download the XL2BB onto my computer unfortunately. Here are some screenshots of my file with fake info that resembles my confidential information I have.