Link to home
Start Free TrialLog in
Avatar of fredericgilbert
fredericgilbert

asked on

Insert a range containing array formulas based on user input

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.


fg
InsertRangeBasedOnCellValue-v1.xlsm
Avatar of Faustulus
Faustulus
Flag of Singapore image

fredericgilbert,
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.
Avatar of fredericgilbert
fredericgilbert

ASKER

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.
Thx



I am sure that what you thinking I need IS what I need.
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.
The array formula is in the Stats!D15 cell and should be copied down until end date's last row.
=getgadata(C3,C15,CONCATENATE(E13,"&",F13,"&",G13,"&",H13,"&",I13,"&",J13,"&",K13),C5,C6,C8,D13,C10,C11,C12)

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.
Source: http://www.automateanalytics.com/p/google-analytics-vba-functions.html
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.
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.

Bye
InsertRangeBasedOnCellValue-v2.xlsm
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, _
maxResults)

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!
ASKER CERTIFIED SOLUTION
Avatar of fredericgilbert
fredericgilbert

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thx EE
I helped myself.