By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

Should I use VB?

P: 1
Hi,

I'm trying to do several things with the goal of automating some reporting, and am not sure what the right tools to use are, so maybe you folks can provide some clarity for me. I'm trying my best and have been trying many different tools, so any help is very appreciated!

Basically, I have a directory full of daily Excel files that I need to open, and merge into a larger sheet.Based on the file namesI need to add a "Date" value to the larger Excel sheet in a column so I know what date file the same from.

Next, I have some canned formulas I wrote I need to have run every time and automatically filled down in their respective column every month

in Column AA
=NETWORKDAYS(R2,Z2,Holidays!$A$2:$A$817)

in Column AB
=NETWORKDAYS(Z2,S2,Holidays!$A$2:$A$568)

in Column AC
=NETWORKDAYS(R2,S2,Holidays!$A$2:$A$876)

in Column AD
=IF(A2=31,"RDC",IF(A2=32, "RDC",IF(A2=38,"RDC","Proposal")))

in Column AE
=IF(AA2>=60,"60 plus",IF(AA2>=30,"30-59 days",IF(AA2>=10,"10 to 29 days",IF(AA2>=2,"2 to 9 late",IF(AA2>=1,"1 Day",IF(AA2=0,"0 days","Advancedl"))))))

Next, I need to automatically screen the data and clean for certain things, like rows in column A that start with 038, 031, 032 need to be pulled to a new sheet, etc.

So, knowing these requirements how would you ladies and gentlemen suggest I proceeed? Is there a tool I should use that has a low enough learning curve for me to use?

Many thanks in advance for your suggestions
May 6 '08 #1
Share this Question
Share on Google+
1 Reply


jeffstl
Expert 100+
P: 432
I would say you want to use VBA, which is essentially visual basic for office applications.

Excel will have tools for writing the code you need. (Hit Alt+F11 to get into the VBA editor within excel) Or go to Tools, Macro, Visual Basic Editor.

If you have not written VB before try to go online and find specific samples of Excel VBA code snippets and try them out so you can see how things work.

When you run into specific problems with code, post the code and the error\problem for help.

I can't point to any particular "good" site for VBA tutorials. However, there are alot of examples out there if you search, including some on this site.

This one might get you started.
Excel VBA Discussion
May 9 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.