PDA

View Full Version : Solved: Setting Variable Equal to Cell Value



ajrob
02-08-2009, 09:12 AM
Hoping this is easy ... I'm having a problem setting integer variable Model equal to the value of a cell. I've highlighted the specific section in blue. For background, I'm trying to add labeling based on a specific order of blank and non-blank cells.

Thanks.

Dim Model As Integer

iRow = 7

Do Until iRow = 500

If IsEmpty(Cells(iRow, 34)) = False And _
IsEmpty(Cells(iRow + 1, 34)) = True And _
IsEmpty(Cells(iRow + 2, 34)) = True Then
iRow = iRow + 1
ElseIf IsEmpty(Cells(iRow, 34)) = True And _
IsEmpty(Cells(iRow + 1, 34)) = True And _
IsEmpty(Cells(iRow + 2, 34)) = False Then
iRow = iRow + 1
ElseIf IsEmpty(Cells(iRow, 34)) = True And _
IsEmpty(Cells(iRow + 1, 34)) = False And _
IsEmpty(Cells(iRow + 2, 34)) = False Then
Model = Cells(iRow, 34)
Select Case Model
Case Model = 15000
Range("B" & iRow).Select
ActiveCell.FormulaR1C1 = "Blue"
Selection.Font.Bold = True
Case Model = 5670
Range("B" & iRow).Select
ActiveCell.FormulaR1C1 = "Red"
Selection.Font.Bold = True
End Select
iRow = iRow + 1
ElseIf IsEmpty(Cells(iRow, 34)) = False And _
IsEmpty(Cells(iRow + 1, 34)) = False And _
IsEmpty(Cells(iRow + 2, 34)) = False Then
iRow = iRow + 1
ElseIf IsEmpty(Cells(iRow, 34)) = False And _
IsEmpty(Cells(iRow + 1, 34)) = False And _
IsEmpty(Cells(iRow + 2, 34)) = True Then
iRow = iRow + 1
ElseIf IsEmpty(Cells(iRow, 34)) = True And _
IsEmpty(Cells(iRow + 1, 34)) = False And _
IsEmpty(Cells(iRow + 2, 34)) = True Then
iRow = iRow + 1
ElseIf IsEmpty(Cells(iRow, 34)) = True And _
IsEmpty(Cells(iRow + 1, 34)) = True And _
IsEmpty(Cells(iRow + 2, 34)) = True Then
iRow = 500
End If
Loop

Bob Phillips
02-08-2009, 09:41 AM
Dim Model As Long

iRow = 7
Do Until iRow = 500

If Not IsEmpty(Cells(iRow, 34)) And _
IsEmpty(Cells(iRow + 1, 34)) And _
IsEmpty(Cells(iRow + 2, 34)) Then

iRow = iRow + 1
ElseIf IsEmpty(Cells(iRow, 34)) And _
IsEmpty(Cells(iRow + 1, 34)) And _
Not IsEmpty(Cells(iRow + 2, 34)) Then

iRow = iRow + 1
ElseIf IsEmpty(Cells(iRow, 34)) And _
Not IsEmpty(Cells(iRow + 1, 34)) And _
Not IsEmpty(Cells(iRow + 2, 34)) Then

Select Case Cells(iRow, 34).Value

Case 15000
With Range("B" & iRow)
.FormulaR1C1 = "Blue"
.Font.Bold = True
End With

Case 5670
With Range("B" & iRow)
.FormulaR1C1 = "Red"
.Font.Bold = True
End With
End Select
iRow = iRow + 1
ElseIf Not IsEmpty(Cells(iRow, 34)) And _
Not IsEmpty(Cells(iRow + 1, 34)) And _
Not IsEmpty(Cells(iRow + 2, 34)) Then

iRow = iRow + 1
ElseIf Not IsEmpty(Cells(iRow, 34)) And _
Not IsEmpty(Cells(iRow + 1, 34)) And _
IsEmpty(Cells(iRow + 2, 34)) Then

iRow = iRow + 1
ElseIf IsEmpty(Cells(iRow, 34)) And _
Not IsEmpty(Cells(iRow + 1, 34)) And _
IsEmpty(Cells(iRow + 2, 34)) Then

iRow = iRow + 1
ElseIf IsEmpty(Cells(iRow, 34)) And _
IsEmpty(Cells(iRow + 1, 34)) And _
IsEmpty(Cells(iRow + 2, 34)) Then

iRow = 500
End If
Loop

ajrob
02-08-2009, 10:07 AM
That did it ... thanks.
Also, I got rid of the variable Model -- no longer required.

Bob Phillips
02-08-2009, 10:27 AM
Also, I got rid of the variable Model -- no longer required.

True, hardly worth using a variable if it is only used once.