If statements question

[gtg689a]

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!

[DKenny]

use the .Offset property of the selected cell.

[gtg689a]

This is what I have written so far, but it is not working:
'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.

[gtg689a]

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

[TheBionicOrange]

try this :

'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

[DKenny]

Try the following
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

[gtg689a]

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

[DKenny]

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

[gtg689a]

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.

[gtg689a]

Nevermind, its good to go!

[TheBionicOrange]

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

[gtg689a]

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!


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

[gtg689a]

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!

[DKenny]

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

[gtg689a]

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.