With rngGrid.Cells(1 + lResCounter, 1 + lCapCounter)
Ath this point the macro has determined the cell to put the data in. I want to write an if statement that says" if the row height is greater than X, move one cell over and append the data.
I've got every part of this if statement written except for this part. All I need to know is how to write the "move over one cell" part of the code.
Thanks!
Last edited by gtg689a; Apr 27th, 2006 at 02:28 PM.
Instead of having 5 columsn for the 5 possible rankings, I have ten columns. I also added 5 options that the Capability array can hold(Blank1, Blank2, Blank3, Blank4, Blank5). But these are not options for someone to choose. They just create a grid that has blank "spillover" columns.
This is the reason why I want to be able to move one cell to the right if the rowheight has gone over 450(I run a macro after every loop that autofits everything).
OK
It look slike you haven't correctly set a reference for the rngCell object variable.
Should rngCell and rngGrid.Cells(1 + lResCounter, 1 + lCapCounter) be the same cell?
if so then try the following...
VB Code:
Set rngCell = rngGrid.Cells(1 + lResCounter, 1 + lCapCounter)
Well it works, but at the same time it doesn't work. *sigh*
I am not really sure how to explain what it is doing wrong, so I am posting it here. It should be inserting all of the names, and then once it inserts a name when the cell row height has reached 350, it should start putting the names in the cell directly to the right of the one it was originally going to put it in.
***However, it is moving the entire contents of the 1st cell over to the cell to the right and appending the new name. It is only supposed to move to the next cell and start appending, not bringing anything with it.*****
WARNING- This macro takes about 15 sec. to run, probably not the most efficient use of code.
Last edited by gtg689a; Apr 18th, 2006 at 10:48 AM.
I wish I could say I wrote all of this, but DKenny wrote 85% of it. Thanks to everyone who helped out.
Out of curiousity(haha).... if I wanted to add a distribution counter at the bottom, how could I do that? In other words, I guess I need something that adds up how many names are in each box on the grid.
Obviously, I've got the main issue solved here, but just in case anyone wants to impart more knowledge!
VB Code:
Sub PerformanceGrid()
Dim rngMgrInput As Range
Dim rngGrid As Range
Dim saEmployee() As String
Dim lRecordNum As Long
Dim vResults As Variant
Dim vCapability As Variant
Dim lResCounter As Long
Dim lCapCounter As Long
Dim rngCell As Range
Dim lHeight As Long
'Build two arrays to hold the options for Results and Capability
Ok, I would like to add a rngCell2 to this code. underneath this grid, I want a distribution list. There are 10 categories of people, based on what box they are in on the grid. Underneath the grid, I would like 10 columns, and in the macro...
In the macro I would like to have If statements that say
If the results counter = X and the CapCounter = Y, then put this person's name on the next line in this column Z.
I don't know how to write that part of the code, and are there any special considerations to writing a ton(25 to be exact) of If statements like that.
Or could I say.. If rescounter = A and Capcounter = B or If rescounter = C and Capcounter = d OR If rescounter = E and Capcounter = F
Then.. put the next name in this column? Thanks!
Last edited by gtg689a; Apr 27th, 2006 at 09:32 AM.
Sure,
Right now the macro from the code above puts everyone's names on this grid that is 5x5. You could further divide this grid into 5 "zones" made up of different boxes. I need to have a Distribution chart at the bottom that has 5 columns (1 for each "zone") and then underneath lists the people in each zone in seperate cells.
So, I figured this could be accomplished because when the macro says... Ok, this person is supposed to be in box "Results 2 by Capabilities 3", I should put them under the next available cell in Zone 3 of the distribution list.
Reply With Quote
