I need help creating this code, the code created works, but take too long to process, over 10 minutes. I need to execute to
In â€śsheet1â€ť how do I find the value generated by formula in ck145:ck148 in every instance from the source range (D5:IT7)
in sheet2 named â€ś Sheet 11 â€ť and copy the contents in this sheet from the cell in the previous column on the same row as
the match to â€śsheet1â€ť, in the CL column starting on (row 145 for ck145 matches) and (row 146 for ck146 matches) and (row
147 for ck147 matches) and (row 148 for ck148 matches) .
Also, I have other worksheets I would like to update the code to query data from into sheet 1.
rngArray = Array("D5:IT7", "D5:IT28", "D5:IT144", "D5:IT645") : sheet 11, 12, 13 and 14
OPArray = Array("ck145:ck148", "ck149:ck152", "ck153:ck156", "ck157:ck160") :sheet1
a = Sheets("sheet " & 11 + n).Range(rngArray(n)).Value : sheet 11, 12,13 and 14
The values in ck145:ck160 are
preselected by a formula and should not be by this code. The code should go and find the matches to the values generated by
the formulas from ck145:ck160 in sheet1, to those in sheet 11, 12, 13 and 14, and place the content from the cell in the
previous column same row in each instance into the CL column of sheet1.
For example if ck145=10 in sheet1, go to sheet 11 and find all matches in the selected range that equal 10, then for each
true occurrence, copy the content in the cell from the previous column on the same row and paste into sheet1 starting at
Use the same function for whatever value is generated (eg. =MAX('Sheet 11'!$D$5:$IT$7)), in ck146, ck147 and ck148. Find the
values from those cells in sheet 11, then for each true occurrence, copy the content in the cell from the previous column on
the same row and paste into sheet1 starting at CL146 for the matches to ck146 and CL147 for the matches to CK147 and CL148
for the matches to CK148.
Also, the content output should be listed in numerical order.
example 1,3 before 6,16 or 9,10 before 13,21
(see partial sample worksheets in the attachment)