Amending Date Range In a Formula From A Macro-UserForm

[amish_z]

Hi

So I will explain in more details, the following formula downloads data from Google Analytics for a certain period :

=Dump(GoogleAnalytics("ga:XXXXXXX","ga:adCost,ga:impressions,ga:adClicks,ga:CTR,ga:CPC,ga:goal7Completions","2015-02-03","2015-03-04","ga:campaign","","ga:medium==cpc;"",1,5000,FALSE,FALSE))

I want to create a button that can look up new date ranges I type in cells and enter them into the formula above, replacing the dates currently in it.

I will be entering the new date ranges in cells "A5" for start date and "A7" for end date.

I know it's quite a complex idea, but if any1 can help I'd be very grateful

 

[amish_z]

I have been trying for some time, I don't think this is possible

Please prove me wrong!

 

[FormR]

Hi don't have the add-in so can't test, but maybe this will work for you.

=Dump(GoogleAnalytics("ga:XXXXXXX","ga:adCost,ga:impressions,ga:adClicks,ga:CTR,ga:CPC,ga:goal7Completions",TEXT(A5,"YYYY-MM-DD"),TEXT(A7,"YYYY-MM-DD"),"ga:campaign","","ga:medium==cpc;"",1,5000,FALSE,FALSE))

 

[amish_z]

Thanks!

I'll give this ago, hope it works!

 

[amish_z]

Hi don't have the add-in so can't test, but maybe this will work for you.

=Dump(GoogleAnalytics("ga:XXXXXXX","ga:adCost,ga:impressions,ga:adClicks,ga:CTR,ga:CPC,ga:goal7Completions",TEXT(A5,"YYYY-MM-DD"),TEXT(A7,"YYYY-MM-DD"),"ga:campaign","","ga:medium==cpc;"",1,5000,FALSE,FALSE))

It tells me that line of code is a Syntax Error, not sure what to do about that...

 

[FormR]

Like I say - difficult to test without the add-in. Do you have a link to the site where you got it from (assuming it's free!)? Does your original formula (in post#1) work?

Second attempt - again not tested.

=Dump(GoogleAnalytics("ga:XXXXXXX","ga:adCost,ga:impressions,ga:adClicks,ga:CTR,ga:CPC,ga:goal7Completions",TEXT(A5,"YYYY-MM-DD"),TEXT(A7,"YYYY-MM-DD"),"ga:campaign","","ga:medium==cpc;",1,5000,FALSE,FALSE))

 

[amish_z]

Yes its free!

You can download it here : http://seotools.nielsbosma.se/releases/]

My original formula works fine, it seems as soon as I change it with something that is not actual dates, it throws up the syntax.

 

[amish_z]

http://seotools.nielsbosma.se/releases/

 

[FormR]

My original formula works fine, it seems as soon as I change it with something that is not actual dates, it throws up the syntax.

I downloaded the Add-In and if I copy and paste the formula from post#1 into Excel I get a syntax error. Can you check that it copy and pastes (directly from the forum) for you?

Having said that - the Add-In has some examples workbooks and it seems that you should be able to simply replace the hard coded dates with the cell references that contain your dates (A5 and A7) - no need for the TEXT() function.

 

[amish_z]

Ok so I've nearly got it working!

I am using this in my Macro :

ActiveCell.FormulaR1C1 = _
"=Dump(GoogleAnalytics(""ga:1169833"",""ga:adCost,ga:impressions,ga:adClicks,ga:CTR,ga:CPC,ga:goal7Completions"",A307,A306,""ga:campaign"","""",""ga:medium==cpc;ga:campaign!=(not set);ga:campaign!=ZZZ_Old_Mexico"","""",1,10000,FALSE,FALSE))"

This formula works fine in Excel but when it runs through the Macro, it amends the A307 to 'A307' - which the Add-in does not recognise.

How can I get VBA to not add ' to the cell I am using?