Insert a range containing array formulas based on user input

Posted on 2013-06-14
Last Modified: 2013-06-24
Hi EE People,

I have found some scripts that partially do what I need but not entirely. After fiddling with them, I have attached the one closer to my needs within my workbook.

Merits of Worksheet_Change script:
- Based on a change in a cell value (UserInput C3), it hides/unhides some cells (DataRange Col A).

Shortcomings of Worksheet_Change script:
- It doesn't work with a formula result (UserInput B3).
- Its displayed range is currently limited to one column of cells (DataRange Col A).
- It hides/unhides individual cells in colunm A instead of inserts/deletes them.

My Needs:
- Script should work with the result of a formula (see UserInput B3).
- Script should insert a range of one row as many time as needed based on the date difference (see UserInput B3).
- The range to be inserted is comprised of array and regular formulas (see Stats 15:15).

- Special case: based on the date difference, if the number of rows to be inserted is less than the number of rows currently displayed in the worksheet, the extra rows should be removed.

Objective: the script should dynamically manage the insertion or removing of rows based on user input in a worksheet.

Thx in advance for your help.

Question by:fredericgilbert
  • 6
  • 4
LVL 14

Expert Comment

ID: 39252735
You should decide whether to use your lovely array formula or code becasue having both doesn't make to much sense. You see, the array formula allows you to enter "code" on the worksheet. In order to do what you wish to do the array formula should be adjusted. You could use code to adjust the array formula but that is rather like asking the mechanic to ask the mechanic to fix your car.
Of course, I have been looking at what I think you want to do rather than at what you asked me to look at, thinking that I would get to a result faster. Perhaps the result I arrived at faster isn't the result you want. I looked exclusive at your Stats sheet and cells C5:C6 to control the number of rows that would be displayed from Stats!Row(15) and below.

Author Comment

ID: 39252960
Hi Faustulus,

Indeed, the Stats sheet is the real concern here. I have put the the other sheets there to show where I am right now with my VBA code.

"looked exclusive at your Stats sheet and cells C5:C6 to control the number of rows that would be displayed from Stats!Row(15) and below."

Exactly: the diff in between C5 and C6 is the number of row to insert below 15:15 on Stats.
This insertion could vary depending on the dates selected (C5, C6).

I am looking forward to your proposal.

I am sure that what you thinking I need IS what I need.
LVL 14

Expert Comment

ID: 39253118
What is the data you need to be entered by the array formula? the formula's job would have to be assigned to code. If the explanation is very complicated, perhaps posting a sample could help to demonstrate.

Author Comment

ID: 39253148
The array formula is in the Stats!D15 cell and should be copied down until end date's last row.

Note: the modules required to make the array formula work are not included within the workbook I posted for the sake of not confusing anyone. If needed, I can upload the workbook with them embeded...

FYI: it is to fetch Google Analytics data.
LVL 14

Expert Comment

ID: 39255820
Try to delete one of the rows containing your array formula. Then try to insert a row within the range covered by it. The array occupied by this formula wants to be treated as a block. It can't be modified, only deleted and replaced.

For your better understanding (and to the extent that I understand it myself) the array formula itself probably decides the number of rows it occupies. Observe that both C5 and C6 are passed to the UDF as parameters. Why they don't have the effect of setting the number of rows as you expect, I can't tell without looking at the code. If it is at all possible I urge you to let the inventor of this system deal with his creation.

To me this looks like a very smart solution that doesn't work. The best way of dealing with it is to implement a less smart solution that does work. Understandably,  that doesn't seem to be what you want. It probably also isn't the easiest way of dealing with the problem.
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.


Author Comment

ID: 39256399
Hello Faustulus,

i understand your point. I am sorry, I should have included the modules before anything, not to waste your time.

I have included the modules required to make the array formula work in the v2 workbook. The modules only control the retrieving of the Google Analytics data and expose it in the the cells "already" populated with the array formula, nothing else.

Hence, the result is limited by the number of cells already populated with the array formula. This is the current limitation.

Per example, let's say that I need 44 days of data, if there is only 2 rows already populated with the array formula, the GA data will fill those 2 rows up no matter if the time period require more (42 rows).  

The insertion script I am looking for is to populate the range (A15:N15) in as many rows as the time period (nb of days) requires (44 in the example above).
Hoping this clarifies the situation.

Note: if you have a website with a GA account, go get your token at AutomateAnalytics, put it in c3 and try it out. Very good tool.

LVL 14

Expert Comment

ID: 39258549
Hello fredericgilbert,
You were right. Supplying the code only confuses the issue. For me, studying it and finding a solution to your problem would mean several hours of work, if not days. The modification should be done by the original code supplier.
I suggest that you rephrase your question. You need some one who understands the array formula. It is this formula that must be correctly inserted. I haven't done such a formula before and this isn't an easy one to start with. Let me try to explain. Your basic function is this:-
Public Function getGAData( _
ByVal authToken As String, _
ByVal profiles As String, _
ByVal metrics As String, _
ByVal startDate As Date, _
ByVal endDate As Date, _
Optional ByVal filters As String, _
Optional ByVal dimensions As String, _
Optional ByVal segment As String, _
Optional sort As Variant = False, _
Optional includeHeaders As Boolean = False, _
Optional includeDimensionColumns As Variant = True, _
Optional maxResults As Long = 10000) _
As Variant

Open in new window

Each line above represents one parameter being passed from the worksheet to the procedure. Observe the "startdate" and "enddate". So, what does the function do? It calls the function getData:-
getGAData = getData( _
authToken, _
profiles, _
metrics, _
startDate, _
endDate, _
filters, _
dimensions, _
segment, _
sort, _
includeHeaders, _
includeDimensionColumns, _

Open in new window

One wonders why the function GetData isn't called from the sheet outright. All the parameters are passed on as they are received. Perhaps not all. I didn't count.
Now the part I don't understand:-
Being a UDF (User Defined Function) it returns one result. The result is a Variant, set to be an array. This array would contain the values to be placed into a number of cells in one row, I presume. However, the function is entered as an array formula, meaning it repeats itself. You enter it once in row 15 and it fills itself down for as far as needed, presumably determined by start and end date. This action is part of the array formula, not the UDF.

You need someone who is familiar with this type of array formula and then, yes, you would be right in not supplying the code because code has nothing to do wth it.
On the other hand, it seems reasonable that the array formula on the worksheet repeats itself for as long as the code supplies results. It would then be possible that the code would be responsible for not supplying the requested number of results.
As for my own role in this matter, I don't know how to set up such a formula and can, therefore, only speculate. Find some one who does understand how this formula is supposed to work and you will be well on the way to have a solution to your problem.
Sorry I couldn't be of more help & best of luck!

Accepted Solution

fredericgilbert earned 0 total points
ID: 39259754
I have found myself an almost complete solution which manage the numbers of rows based on cell value I needed.

The solution manage (insert/delete) a variable number of rows filled with a mix of array and regular formulas based on a cell value (number of days between start and end dates)  

I have still one action left to do which is to delete first then insert rows
when the number of days has changed in the Stats sheet.
I plan to make it work today.
In the meatime, I consider my question answered by me.
A workbook is posted with my almost completed working solution.


Author Comment

ID: 39259760
Thx EE

Author Closing Comment

ID: 39270687
I helped myself.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now