 |
| Excel VBA Discuss using VBA for Excel programming. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
April 12th, 2016, 06:13 PM
|
|
Registered User
|
|
Join Date: Apr 2016
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
|
|
Formulas in VBA
Hello all,
I am fairly new to VBA, just to get that out there.
I have 33 sheets (all set up the same) with a variety of formulas pulling information from other pages. I would like to know how, if it is even possible to run all these formulas in VBA and just output the result to the cells. My hopes is that it will speed up the sheets when calculations are performed.
so basically I have
Code:
=COUNTIFS(Events!$A:$A,$A$1,Events!$G:$G,$A31,Events!$B:$B,C$30)
stretching through C31:S82 and again from W31:AU82 and then
Code:
=IFERROR(MEDIAN(IF(Events!$A:$A=$A$1, IF(Events!$B:$B=B4,IF(Events!$H:$H=$C$2,Events!$D:$D)))),"")
in another 2 sections of the sheet. These spread across 33 sheets gets painstakingly slow. any help to speed things up is appreciated.
Thanks,
|
|
April 13th, 2016, 04:41 PM
|
|
Authorized User
|
|
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
|
|
Hi, it is possible.
VBA has a formula property where you make entries that will appear as a formula.
Now the question is are the sheets in one document and if the sheets have the default names like Sheet1, sheet2, sheet3 and so on?
If you answer is yes from the above then a For loop will help.
If not then a For Each loop across the sheets will help.
__________________
Nostalgia 4 Infinity
|
|
The Following User Says Thank You to Zakalwe For This Useful Post:
|
|
|
April 13th, 2016, 05:02 PM
|
|
Registered User
|
|
Join Date: Apr 2016
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
|
|
Yes,
The sheets are in the same document. They are all different names however.
Ok, I see how to do a loop, however, the formula changes for each cell,
Example-
Code:
=COUNTIFS(Adhocs!$C:$C,$A$1,Adhocs!$E:$E,$A32,Adhocs!$D:$D,C$30,Adhocs!$A:$A,"COMPLETE")
=COUNTIFS(Adhocs!$C:$C,$A$1,Adhocs!$E:$E,$A33,Adhocs!$D:$D,C$30,Adhocs!$A:$A,"COMPLETE")
likewise it moves to the column also
Code:
=COUNTIFS(Adhocs!$C:$C,$A$1,Adhocs!$E:$E,$A32,Adhocs!$D:$D,C$30,Adhocs!$A:$A,"COMPLETE")
=COUNTIFS(Adhocs!$C:$C,$A$1,Adhocs!$E:$E,$A32,Adhocs!$D:$D,D$30,Adhocs!$A:$A,"COMPLETE")
The changing columns would be station names for comparison, and the changing rows are weeks of the year.
how would one run a loop on changing formulas like that?
Last edited by Loki81; April 13th, 2016 at 05:17 PM..
|
|
May 27th, 2016, 02:10 PM
|
|
Authorized User
|
|
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
|
|
Hi Loki,
Sorry I never received your reply in my mailbox.
Can you email me a sample?
[email protected]
zweniryu at gmail dot co dot za
__________________
Nostalgia 4 Infinity
|
|
The Following User Says Thank You to Zakalwe For This Useful Post:
|
|
|
May 31st, 2016, 09:15 PM
|
|
Registered User
|
|
Join Date: Apr 2016
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
|
|
I have already rewritten the macro and found a way around the errors I was getting. Thanks!
What I did was rewrote it to export to another sheet from a template. And a bit of frustration was able to get it to calculate all the sheets in less than 5 mins. Thanks for the thought though! I am sure I will use the forum for further questions in the future.
Thanks Again,
|
|
|
