VBA help! (Macro Disaster)

altsean06

New Member
I will try to explain this as well as possible.
Have three Named Ranges. These named ranges consist of multiple rows. They are complete rows. So in order to have multiple groups of these named ranges, they must be arranged vertically. heres an example of my datasheet. I have taken out many associates to save space.
b c d

1 Associates Feb
2 Month Goal 90
3 FullTime Goal 4
4 Altherr 1
5 Ayling 2
6 Bolin 4
7 Buntain 3
8 total 10
9 part time goal 3
10 Collins 11
12 Hower 2
13 Leslie 3
14 Total 16
15 DStatus Goal 2
16 Beebee 5
17 Buggerman 4
18 Chapman 2
19 Lazar 9
20 Total 20

Column D -N continue with the months, associate goals and the associate actuals. The total rows are Sum Functions. and in column O they are Sum Functions across the rwos to get the associate total for the year. Currently with this example, rows 4-7 are named FullTime, rows 10-13 are named PartTime, and rows 16-19 are named DStatus. What i need is a macro that inserts a new rown into the named range one row above the last associate in each range-. But i need the macro to be flexible that it understands that because i hired and thus added new assoicates, that it changes the location to insert the row based upon the corresponding number of rows in the range... Please help!

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Norie

Well-known Member
How would you be determining where to out the new row?

I think that would need to be known if you wanted VBA to help.

Is there some sort of pattern to the data? Or perhaps a delimiter?

altsean06

New Member
The row should be inserted so that when it is inserted, it ends up being the row above the last row in the range. IE Buntain, Leslie, and Lazar would each end up with a new row above them. Let me clarify though, i would end up having three macros, one for each range. After the macro inserts the row and types the constatn data (conditional formatting, formulas, and a standard "'---------" value as their performance) The user will type in the new Associates name and whatever performance data they have acquired...

How would you be determining where to out the new row?

I think that would need to be known if you wanted VBA to help.

Is there some sort of pattern to the data? Or perhaps a delimiter?

* Bump*

Norie

Well-known Member
What's the criteria/ logic behind the rows being inserted above the names you mention?

Also you've now mentioned formulas, conditional formatting etc...

Does that mean you don't just want to insert a blank row? And why do you think you need 3 macros?

altsean06

New Member
The criteria is that when i hire a new associate, they fall under one of three categories. Full Time Part Time or D Status. So i planned on putting one macro button for each range. If i hire a FullTime person, i click the button assigned to macro , and presto a row has appeared above the last person in that range. This is because when you insert the row there, the conditional formatting extends, and my sum function (for the totals rows) will also adjust from =SUM(D4:D7) to =Sum(D4:D8). When we hire new people we only hire them one at a time and in whatever position is available, so i figured three seperate macros is best. I will then also have a macro that will sort the data alphabetically by last name.

Basically i will hire someone. Then i need to track their performance. So i make a spot for them by inserting a row and inputing their name. I want them to have a value of " '--------- " so that they are not counted in any months. its my base value for everyone. Then, the first of the next fiscal month after they are hired, they start opening credits. If they open 3 then i click the box for that month, under their name, and put 3. It adds to the ranges total (like total Full Time), then to the stores total for that month, and finally to their own total for the year.

Im sorry let me explain this. I know enough about excel that i can very quickly insert a new row by using the toolbar or just right clicking in the row below. i can also extend the formulas quickly and easily, but my boss cannot. she is not excel savvy or even computer savvy. im attempting to have a finished project for her before i deploy to afghanistan so that her paperwork is more or less automated and my absense is not as straining as it could have been.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,661
Messages
5,838,651
Members
430,560
Latest member
DaliaKenawy86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back