Hi All,
Seeking some excel Guru help!
I'm pretty new to VBA, but have a lot of experience using excel.
I have a sheet with two sheets;
1. PASTE
2. ANALYSIS
PASTE LOOKS LIKE THIS -
<colgroup><col span="2"></colgroup><tbody>
</tbody>
Now in reality the sheet has more rows and more columns, however for this example i only need this,
In Analysis tab there are
DATE
03/06/2013
04/06/2013
05/06/2013
06/06/2013
07/06/2013
08/06/2013
09/06/2013
Dates,
Now in B2 I have a rather complicated SUM, using COUNTIFS, which only take corresponding countries for the date;
=SUM(COUNTIFS(PASTE!B:B,"Info-no",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-fi",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-uk",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-nl",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-bg",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-se",PASTE!A:A,ANALYSIS!A2),COUNTIFS(PASTE!B:B,"Info-pl",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-de",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-hu",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-lt",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-ru",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-pt",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-cz",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-gr",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-lv",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-ro",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-fr",PASTE!A:A,ANALYSIS!A2))
This sum works fine when pasted and dragged down.
My problem!!
If i record a macro and paste this into recorder and run the macro i get an;
application-defined or object-defined error
!! WHY?! Can anyone please help!
Seeking some excel Guru help!
I'm pretty new to VBA, but have a lot of experience using excel.
I have a sheet with two sheets;
1. PASTE
2. ANALYSIS
PASTE LOOKS LIKE THIS -
Resolved Date | Country inbox |
03/06/2013 | Info-it |
03/06/2013 | Info-it |
03/06/2013 | Info-no |
03/06/2013 | Info-fi |
03/06/2013 | Info-no |
03/06/2013 | Info-dk |
03/06/2013 | Info-uk |
03/06/2013 | Info-nl |
03/06/2013 | Info-bg |
03/06/2013 | Info-se |
03/06/2013 | Info-no |
03/06/2013 | Info-nl |
03/06/2013 | Info-se |
03/06/2013 | Info-dk |
03/06/2013 | Info-nl |
03/06/2013 | Info-se |
03/06/2013 | Info-dk |
03/06/2013 | Info-no |
03/06/2013 | Info-nl |
03/06/2013 | Info-it |
03/06/2013 | Info-no |
03/06/2013 | Info-no |
03/06/2013 | Info-se |
03/06/2013 | Info-se |
03/06/2013 | Info-it |
03/06/2013 | Info-pl |
03/06/2013 | Info-no |
03/06/2013 | Info-nl |
03/06/2013 | Info-it |
03/06/2013 | Info-uk |
03/06/2013 | Info-it |
03/06/2013 | Info-pl |
<colgroup><col span="2"></colgroup><tbody>
</tbody>
Now in reality the sheet has more rows and more columns, however for this example i only need this,
In Analysis tab there are
DATE
03/06/2013
04/06/2013
05/06/2013
06/06/2013
07/06/2013
08/06/2013
09/06/2013
Dates,
Now in B2 I have a rather complicated SUM, using COUNTIFS, which only take corresponding countries for the date;
=SUM(COUNTIFS(PASTE!B:B,"Info-no",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-fi",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-uk",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-nl",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-bg",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-se",PASTE!A:A,ANALYSIS!A2),COUNTIFS(PASTE!B:B,"Info-pl",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-de",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-hu",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-lt",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-ru",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-pt",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-cz",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-gr",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-lv",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-ro",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-fr",PASTE!A:A,ANALYSIS!A2))
This sum works fine when pasted and dragged down.
My problem!!
If i record a macro and paste this into recorder and run the macro i get an;
application-defined or object-defined error
!! WHY?! Can anyone please help!