I used the record macro tool to get an idea of what I want to do in code. I am having trouble modifying it though to suit my needs when the selection of cells for the array changes.
Range("D1:D23").Select
Selection.FormulaArray = _
"=RC[-3]:R[22]C[-3]+'[South DDACTS Zone Tally_R20130910.xlsx]APlt'!R149C9:R171C9"
In the above, I selected a range of cells D1:D23 where I will be outputting my final array into. I typed '=' into the function bar and then selected a set of cells (on a worksheet "sheet3") which were A1:23. I typed '+' into the function bar and then selected a second set of cells on a separate workbook which were I149:I171. I pressed CTRL+SHIFT+ENTER to make the function bar my formula and it spit out the new values into D1:D23.
The function bar looks like this:
=A1:A23+'[South DDACTS Zone Tally_R20130910.xlsx]APlt'!$I$149:$I$171
This macro would be great if I did not need to have the ability to change the second set of cells that I added to the first.
In the macro I am working on, I take a value from a sheet in one workbook and search a second workbook for that value. When that value is found, I need to select a range of cells (always 23 cells) in the column that I found my vaule in that starts two rows below the cell where I found the value (call this arrayA). I need to take arrayA and add it to A1:A23, or arrayB, (which is a fixed location from the other spreadsheet). That new array needs to be written over the place of arrayA.
Any help is appreciated.
Bookmarks