I'm trying to learn VBA programming.
I got some help here earlier today, so I'll try again.
Is it possible to create a macro/VBA containing certain functions, that I can always activate in any worksheet?
The case is, that I use a data mining program (SPAD), which returns results in an Excel Workbook (with 7 worksheets).
I would like to record a macro, write a VBA script or something, that ideally allows me to press a button when I'm in worksheet1 ("Corem-1") of this workbook, thereby adding a range of functions to the different worksheets making some calculations.
Can that be done?
To be more specific:
I would love to create a macro, a program, a VBA script, i.e. just some thing, that can run a range of calculations and add values to cells and so on in a result workbook returned from a Data mining program.
In the first worksheet "Corem-1" following two things should be added to the following areas:
In the range "F4:F3000" the program should copy something doing the same as this normal Excel function:
IF($D4<>"Illustrative";"";(LOOKUP(REPT("Z";255);$D$3:$D3)="Count after cleaning")+0)
and in the range "G3:G3000":
IF($B3="count before cleaning"; ""; if($B3>0;1;" "))
In Corem-4 the second worksheet the following calculations/ text-values should be added to these cells/ areas:
E1 <-- "Q ="
F1 <-- =count.if('Corem-1'!$D:$D;"Count after cleaning")
G1 <-- "K ="
H1 <-- =SUM('Corem-1'!G3:G3000)
I1 <-- "Total Varians ="
J1 <-- =SUM(B4:B3000)
E2 <-- "Q' ="
F2 <-- =F1-SUM('Corem-1'!F4:F3000)
G2 <-- "K' ="
H2 <-- =count(A4:A3000)
I2 <-- "gns-λ ="
J2 <-- =J1/(H2-F2)
E3 <-- "Mod.vaerdi"
F3 <-- "Mod.rate"
G3 <-- "Kum.rate"
G4 <-- =$F$4
E4:E3000 <-- =if($B4>=$J$2;($B4-$J$2)^2;"")
F4:F3000 <-- =if(not($E4="");$E4/SUM($E$4:$E$3000);"")
G5:G3000 <-- =if(not($F5="");$G4+$F5;"")[/CODE]
Is this possible?
Yes it is possible. I'd suggest using the macro recorder to begin with. From there if you need any specific modifications made, you could start a thread with those particular questions.
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
Wow! I never really got the thing about record macro's, but that very helpful!
Now I have a long text string (copied to notepad), that has been run - it should work.
What I want to do now is to make a 'global' macro, that I can run from, e.g. the Quick Access Toolbar, every time I'm in one of these result workbooks..
How do I do that?
Google personal.xls. You can use this workbook to store macros you want to be available in any instance of excel that you have open, and to any workbook.
As a note if you are storing a recorded macro in the personal.xls workbook, you'll have to change some of the references to be more generic, ie Workbooks("Book1") should be change to Activeworkbook. If you are running into specific errors and can't figure them out, feel free to ask.
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks