If statements question

VB Code:

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!

use the .Offset property of the selected cell.

This is what I have written so far, but it is not working:

VB Code:

'Determine which cell in the grid the employee belongs to
                        With rngGrid.Cells(1 + lResCounter, 1 + lCapCounter)
                        
                            If rngCell.RowHeight > 450 Then
                              
                              With rngGrid.Cells(1 + lResCounter, 1 + lCapCounter).Offset(0, 1)
                                     .Value = .Value & Chr(10) & saEmployee(1, lRecordNum)
                            
                            End With
                            Else
                            
                        
                            .Value = .Value & Chr(10) & saEmployee(1, lRecordNum)
                                                         
                            End If
                            
                            
                            
                                                                                                                 
                        End With
                        
                    End If

It is crapping out and giving me the error message "Object Variable or With Block Variable not set".

Thanks for your help Declan,
I have almost finished this thing up, I will definitly post the finish product if anyone else want to use it.

BY THE WAY, probably pretty important here.

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).

try this :

VB Code:

'Determine which cell in the grid the employee belongs to
                        with rngGrid.Cells(1 + lResCounter, 1 + lCapCounter)
                        
                            If rngCell.RowHeight > 450 Then
                              
                              rngGrid.Cells(1 + lResCounter, 1 + lCapCounter).Offset(0, 1)
                                     .Value = .Value & Chr(10) & saEmployee(1, lRecordNum)
                            
 
                            Else
                            
                        
                            .Value = .Value & Chr(10) & saEmployee(1, lRecordNum)
                                                         
                            End If
                            
                            
                            
                                                                                                                 
                        End With
                        
                    End If

Try the following

VB Code:

With rngGrid.Cells(1 + lResCounter, 1 + lCapCounter)
                            If rngCell.RowHeight > 450 Then
                                     .Offset(0, 1).Value = .Value & Chr(10) & saEmployee(1, lRecordNum)
                            Else
                                .Value = .Value & Chr(10) & saEmployee(1, lRecordNum)
                            End If
                        End With

The bionic orange, your way produced a syntax error

Declan, your way produced the same error as before and highlighted this line when debugging:

If rngCell.RowHeight > 450 Then

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)
                        With rngCell
                            If .RowHeight > 450 Then
                                     .Offset(0, 1).Value = .Value & Chr(10) & saEmployee(1, lRecordNum)
                            Else
                                .Value = .Value & Chr(10) & saEmployee(1, lRecordNum)
                            End If
                        End With

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.

Nevermind, its good to go!

You all sorted now ?
If so ... its probably worth posting your code in case anyone else comes across a similar problem.

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
    vResults = Array("Consistently/Sustainable Exceeded", "Partially Exceeded", "Achieved", "Partially Achieved", "Not Achieved")
    vCapability = Array("Unsatisfactory", "Blank1", "Needs Improvement", "Blank2", "Meets Expectations", "Blank3", "Exceeds Expectations", "Blank4", "Excellent", "Blank5")
    
    'Get the Input and output ranges
    Set rngMgrInput = ThisWorkbook.Worksheets("Manager Input").UsedRange
    Set rngGrid = ThisWorkbook.Worksheets("Performance Grid").Range("B7:K11")
    
    'Resize the employee array to hold 3 pieces of data for each employee Name, Results and Capability
    ReDim saEmployee(1 To 3, 1 To rngMgrInput.Rows.Count - 2)
    
    'Loop through the input table, adding each employee, their result, and capability
    For lRecordNum = 1 To rngMgrInput.Rows.Count - 2
        saEmployee(1, lRecordNum) = rngMgrInput.Cells(2 + lRecordNum, 1)
        saEmployee(2, lRecordNum) = rngMgrInput.Cells(2 + lRecordNum, 6)
        saEmployee(3, lRecordNum) = rngMgrInput.Cells(2 + lRecordNum, 11)
      
    Next lRecordNum
    
    'Clear the output grid
    rngGrid.ClearContents
    
    'Loop through each record in the Employee array
    For lRecordNum = 1 To UBound(saEmployee, 2)
        
        'Loop through each record in the Results array
        For lResCounter = LBound(vResults) To UBound(vResults)
            
            'Loop through each record in the Capability array
            For lCapCounter = LBound(vCapability) To UBound(vCapability)
                    
                    'If the current employee has both the current Results
                    'and Capability value then add them to that cell in the grid
                    If saEmployee(2, lRecordNum) = vResults(lResCounter) _
                    And saEmployee(3, lRecordNum) = vCapability(lCapCounter) Then
                        
                        'Determine which cell in the grid the employee belongs to, if the row height is above 350, switch to the next cell over
                                 Set rngCell = rngGrid.Cells(1 + lResCounter, 1 + lCapCounter)
                        With rngCell
                            If .RowHeight > 350 Then
                                       With .Offset(0, 1)
                                        If .Value = "" Then
                                        .Value = saEmployee(1, lRecordNum)
                                         Else
                                        
                                          .Value = .Value & Chr(10) & saEmployee(1, lRecordNum)
                                          End If
                                     End With
                                   
                            Else
                                .Value = .Value & Chr(10) & saEmployee(1, lRecordNum)
                            End If
                        End With
                        End If
                    
                    'run the "fitnicely" macro
                    Application.Run "FitNicely"
                    
            
            Next lCapCounter
        Next lResCounter
    Next lRecordNum
    
    
    Sheets("Performance Grid").Select
    
    
    'Application.Run "CompensationGrid"
End Sub

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!

Gtg
Can you explain this a little more? I don't understand what exactly you are after here.

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.