Need help with Macros in Excel!

[Vallerina 2]

It's been about 7 years since I've had to deal with Macros other than clicking buttons and having them do what I want them to do. I have to create a Macro to do calculations for many many people.

Does anybody out there feel like being really nice to me today?

There's a thin line between Saturday night and Sunday morning

[nerd137 0]

Can you be a little more specific about what you need done?

[Vallerina 2]

Certainly! Basically, I need to run everyone in the "data" sheet through the "calc" sheet. It needs to total everyone (the bold total at the top references what it needs to add up for everyone.) It also needs to be able to go to a specific person's calc (ie, I'll need to be able to type in somewhere the person's SSN or their Name and have it pull up their info in the calc sheet.)

Does that make sense?

There's a thin line between Saturday night and Sunday morning

[nerd137 0]

Yeah... gimme a few minutes...

[Vallerina 2]

Thanks! I'll be back later (meeting until 5...yuck.)

There's a thin line between Saturday night and Sunday morning

[nerd137 0]

Ha! That was like a test.

You really don't want or need any macros to accomplish your goals. You just need functions. I did some stuff to your sheet to get you started, but it looks like that workbook isn't an exact copy of what you're using. (Of course a good idea... to protect your data.) Anyway, when I saw that, for example, cell D25 simply equals C25 on your Calc sheet, I assumed that's not actually correct. Those aren't supposed to be the same value... are they?

What I did:

-The name field on the calc sheet now has a drop down that relates the names in the Data sheet. If you add or remove names, the list will self adjust. However, these names *must* remain in Column B of the data sheet.

-I created a formula, you'll find in cell B5...

=INDEX(EmpData,MATCH($B$4,EmpNames,FALSE),MATCH(A5,DataHeader,FALSE))

...this formula looks up the name selected from the list and the field name to the left (e.g. "DOB") and returns the appropriate value. This means that the field name on the calc sheet must be exactly the same as the header on the data sheet. So DOB must be DOB. (But it's not case-sensitive.) You can copy this formula exactly as it is written into any cell and it will look up the field name in the cell directly to its left, and return the value from the data sheet.

This formula won't work for any of your cells below row 22 because those refer to the data sheet differently. Instead, use...

=INDEX(EmpData,MATCH($B$4,EmpNames,FALSE),MATCH("fSalaryA"&A25,DataHeader,FALSE))

...which looks up the name from the list and the year in column A and then finds the appropriate data for 'fSalaryA' + the year (e.g. fSalaryA2007, fSalaryA2006, etc.)

Anyways, I hope that helps more than it confuses you. It would be easier to do this if I had real data, but I understand why you can't do that.

Nick

[nerd137 0]

Oh yeah... EmpData, EmpNames, DataHeader are all named ranges I added.

[Vallerina 2]

Ooh! That works GREAT for looking up a specific person. However, the "Total" in cell G1 needs the total for every person listed. So, in the example, it needs the cell D63 for every person to be added up. That's why my boss wants the macro. We have a lot of people that need to be added up. I think I could do all of the calculations in rows and whatnot, but I'm not allowed. I have to use a macro

There's a thin line between Saturday night and Sunday morning

[nerd137 0]

Which column (or columns) of data on the 'Data' sheet needs to be added up?

[Vallerina 2]

Let me repost the excel sheet (I think I posted the wrong one before...)

In the "calc" sheet, the cell D63 needs to be totalled for everyone. It's not in the data sheet since it needs to be calculated.

There's a thin line between Saturday night and Sunday morning

[Vallerina 2]

Bump!

I'll throw two jumps (non-student jumps) on account for someone who can help me out!

There's a thin line between Saturday night and Sunday morning

[Zarkon 0]

If it's an emergency and nerd137 hasn't responded I can take a look at it for ya when I'm on lunch or something later.

[Vallerina 2]

I love dz.com

There's a thin line between Saturday night and Sunday morning

[Zarkon 0]

Sorry, I'm not used to report development using Excel. I typically work with database reporting tools(Cognos,Crystal,SSRS etc). From what I can see your trying to create a formula based on the High 5/10 w-2(C25:C36), but from your post above you want that specific to one person selected in the drop down. I think you could create a third tab for summary and have that do the report for everyone total, and then pull the value into the calc sheet. Or create a summary row on the data sheet and have a name 'All' or something that you could select in the drop down for a summary. I'm not familiar with the data or report requirements so it's hard to advise, but having a sheet for one employee/customer and trying to create an aggregate field will be difficult without a summary column/sheet to reference. Am I misunderstanding?

[Vallerina 2]

Hmmm....I don't think that will quite work. Since the value we need to total is in the "calc" sheet, we can't just do a summary of the "data" sheet. Each person needs to be ran through the "calc" sheet, and we need to add all of those up. They need to be ran through the "calc" sheet so that we can test them (and it's a sheet we use for our files.)

So, yeah. Basically, I still need a macro to run each individual through the calc sheet and total their amounts up.

There's a thin line between Saturday night and Sunday morning

[Zarkon 0]

Ok, if that's the route your going then you could probably just have it loop through all the names on the data sheet and place them in the calc name field. Have the rest of the fields query the data sheet for that particular person, which I think you have already, and just sum that total field into a variable. Once complete set that field to that value(no formula), and it'll remain even if you pull up other people's reports. Make sense?

[Vallerina 2]

Yes, but I have no idea how to do that!

There's a thin line between Saturday night and Sunday morning

[Zarkon 0]

np, can you post a copy of your XLS with the changes? Three above are wrong, wrong(changed), and correct versions right?

[Vallerina 2]

Like this?

There's a thin line between Saturday night and Sunday morning

[Zarkon 0]

Give this a shot and let me know what you think. It's quick and dirty, but I think it's what your looking for.

[Vallerina 2]

Yes!!! Is there a way to include that first thing...with the drop down so I could see specific people?

There's a thin line between Saturday night and Sunday morning

[Zarkon 0]

Should work, because the macro is only placing a value in there...but I dont know much about how that part works. Try copying it from the other ver and see what happens

[opurt 0]

I took a crack at it too.

The drop-down box is put on the sheet from the Forms toolbar (View->Toolbars->forms).

The values in yellow are values from the drop-down box. The values in green are the few values the macro is loading from the Data sheet. The peach field is a value from the Data sheet I wasn't sure where to put.

The drop down box runs the macro "CaptureUser()". I set what macro the drop down box should run when I right-clicked on the drop-down box and selected "Assign Macro". The macro is just some simple VBA code.

Go to Tools->Macro->Visual Basic Editor, and you'll see the "CaptureUser()" macro. I've heavily commented the relatively simple function to give you an idea of what it's doing.

I'm an intermediate Excel user, but know some VBA. I got the combo box information from this site:
http://www.techonthenet.com/excel/questions/create_combo.php

This should get you started. There is one drawback to this method: You have to update the formula on the Calc sheet whenever you add a new employee on the Data sheet; and you have to update the values the combo box is linked to, and displays, when you add a new employee. But, that's no big deal really.

[Vallerina 2]

Nevermind....

There's a thin line between Saturday night and Sunday morning

[Vallerina 2]

Okay, I lied. The macro doesn't seem to be working right with the drop down. Arg!!!!! It seems to be coming up with a big number.....umm...yeah....any thoughts on how to get both together?

There's a thin line between Saturday night and Sunday morning