Macro to (i) drag and drop & (ii) input array formula

Hi,

I need help writing macros to do the following specific functions:

1) I have a program from which I can retrieve data and export the data as an Excel spreadsheet. Now, based on the search parameters I use in the program, the data to be exported into Excel has the same number of columns, but the number of rows can differ. I need a macro to work on any spreadsheet, independent of how many rows of data that spreadsheet may have. And this macro needs to do the following:

Suppose the spreadsheet when initially retrieved has 4 columns worth of data. I input a column between A and B, so now the spreadsheet's applicable columns are A-N-B-C-D, where N represents New column. I need to populate N with values, but these values are a function of column A. Now, I have already created a macro that (i) creates N and (ii) populates the first non-header cell in N (N2) with a value that is a function of cell A2. What I need is a macro to populate cells N3-Nx, based on the values in cells A2-Ax, where x is the number of rows for which there is data in column A. This macro will replace me having to manually drag the formula in cell B2 all the way down to cell Bx. Once again, this macro needs to work regardless of how many rows of data are in a spreadsheet.

2) The second macro involves me inputting a complicated multiple-sum array formula into a cell. This formula has multiple "if" statements, and also references other sheets in the workbook. It does not reference other workbooks. I tried the record method. I pressed record, typed in the formula, but then when I pressed enter, I got a "Could Not Record" message. So I copied the formula into Word, and then pressed record in Excel, pasted the formula from Word into the Excel cell, but then when I went to convert it into an array by pressing F2 and Ctrl+Shift+Enter, I got the "Could Not Record" message again. Is there a macro that will let me input such an array formula into a specific cell on the designated sheet?

Thanks in advance for your help. I hope I have explained clearly what I need to macros to do.

Hi

1) Having put in the formula into B2 do

Please Login or Register  to view this content.

2) If you can put the formula directly into the cell and it works OK, then should be able to do it with code. Can you give us the formula and the range it would have to cover. Is it array entered across the entire range, or array entered into a single cell and copied across the range.


rylo

Hi

The reason the macro recording is falling over is that the formula is too long. I can't get it to array enter directly from code either.

A couple of options
1) Somehow make the formula smaller
2) Have code put the formula in as text without the "=". The cell will take the string, but not as a formula.

If you take 2, then you would have to manually edit the formula, add the "-", then array enter. Filling the range could either be done manually or with code (but only after the formula entry).

HTH

rylo