Saladsamurai
10-23-2009, 08:19 AM
I have the following code. It works great so long as user inputs a number, but if they hit cancel theye get a type mismatch.
I think that Inputbox Cancel returns a 'zero length string' so it is choking sice I have the variable assigned to the input box dimmed as Double.
How to remedy?
Sub RackSummary()
Dim T_critical As Double
Dim i As Long
Dim j As Long
Dim k As Integer
Dim nRow As Long
Dim nSheet As Integer
Dim Summary As Worksheet
Dim ThisCell As String
Dim Space As Integer
Dim msg As String
nSheet = 9 '# of WorkSheets (Not including "Summary"). Summary must be last sheet
nRow = 250
Space = 5 'spaces out data neatly. You can adjust this and see what happens
T_critical = InputBox(Prompt:="Enter Maximum Rack Inlet Temperature Please.", _
Title:="Rack Inlet Temperature", Default:="80.6")
Dim CaseArray() As Variant 'Holds 'titles' of each case. Must be in same order as each corresponding worksheet
'For EX: "Benchmark" is the 1st wrksht tab, "Cooler is the 2nd", and so on
CaseArray = Array("BenchMark", "Cooler 1 - 3", "Cooler 1 - 8", "Cooler 1 - 14", _
"Cooler 2 - 4", "Cooler 2 - 10", "Cooler 3 - 3", "Cooler 3 - 8", "Cooler 3 - 12")
Set Summary = Worksheets("Summary")
'Delete and clear any existing data/highlighting
Summary.Range("A3:FZ250").Delete
Summary.Range("A1:FZ250").Interior.ColorIndex = xlNone
For k = 1 To nSheet
j = 6
Summary.Cells(j, 1 + Space * (k - 1)) = CaseArray(k - 1)
Summary.Cells(j, 1 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 1 + Space * (k - 1)).Value = "Rack"
Summary.Cells(j + 1, 1 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 2 + Space * (k - 1)) = "Inlet T"
Summary.Cells(j + 1, 2 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 3 + Space * (k - 1)) = "Cold CI"
Summary.Cells(j + 1, 3 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 4 + Space * (k - 1)) = "Hot CI"
Summary.Cells(j + 1, 4 + Space * (k - 1)).Font.Bold = True
For i = 2 To nRow
ThisCell = LTrim(Worksheets(k).Cells(i, 1))
If Left(ThisCell, 2) = "R/" Then
Summary.Cells(j + 2, 1 + Space * (k - 1)) = Worksheets(k).Cells(i, 1)
Summary.Cells(j + 2, 2 + Space * (k - 1)) = Worksheets(k).Cells(i, 10) * (9 / 5) + 32
Summary.Cells(j + 2, 3 + Space * (k - 1)) = Worksheets(k).Cells(i + 2, 16)
Summary.Cells(j + 2, 4 + Space * (k - 1)) = Worksheets(k).Cells(i + 1, 17)
j = j + 1
End If
Next i
Next k
For k = 1 To nSheet
For i = 8 To nRow
For j = 1 To (nSheet * Space)
If Round(Summary.Cells(i, 2 + Space * (k - 1)), 1) >= T_critical Then
Summary.Cells(i, 2 + Space * (k - 1)).Interior.ColorIndex = 36
End If
Next j
Next i
Next k
msg = "All Inlet Temperatures That Exceed Desired Maximum Inlet Temperature Have Been Highlighted."
MsgBox msg
End Sub
I think that Inputbox Cancel returns a 'zero length string' so it is choking sice I have the variable assigned to the input box dimmed as Double.
How to remedy?
Sub RackSummary()
Dim T_critical As Double
Dim i As Long
Dim j As Long
Dim k As Integer
Dim nRow As Long
Dim nSheet As Integer
Dim Summary As Worksheet
Dim ThisCell As String
Dim Space As Integer
Dim msg As String
nSheet = 9 '# of WorkSheets (Not including "Summary"). Summary must be last sheet
nRow = 250
Space = 5 'spaces out data neatly. You can adjust this and see what happens
T_critical = InputBox(Prompt:="Enter Maximum Rack Inlet Temperature Please.", _
Title:="Rack Inlet Temperature", Default:="80.6")
Dim CaseArray() As Variant 'Holds 'titles' of each case. Must be in same order as each corresponding worksheet
'For EX: "Benchmark" is the 1st wrksht tab, "Cooler is the 2nd", and so on
CaseArray = Array("BenchMark", "Cooler 1 - 3", "Cooler 1 - 8", "Cooler 1 - 14", _
"Cooler 2 - 4", "Cooler 2 - 10", "Cooler 3 - 3", "Cooler 3 - 8", "Cooler 3 - 12")
Set Summary = Worksheets("Summary")
'Delete and clear any existing data/highlighting
Summary.Range("A3:FZ250").Delete
Summary.Range("A1:FZ250").Interior.ColorIndex = xlNone
For k = 1 To nSheet
j = 6
Summary.Cells(j, 1 + Space * (k - 1)) = CaseArray(k - 1)
Summary.Cells(j, 1 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 1 + Space * (k - 1)).Value = "Rack"
Summary.Cells(j + 1, 1 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 2 + Space * (k - 1)) = "Inlet T"
Summary.Cells(j + 1, 2 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 3 + Space * (k - 1)) = "Cold CI"
Summary.Cells(j + 1, 3 + Space * (k - 1)).Font.Bold = True
Summary.Cells(j + 1, 4 + Space * (k - 1)) = "Hot CI"
Summary.Cells(j + 1, 4 + Space * (k - 1)).Font.Bold = True
For i = 2 To nRow
ThisCell = LTrim(Worksheets(k).Cells(i, 1))
If Left(ThisCell, 2) = "R/" Then
Summary.Cells(j + 2, 1 + Space * (k - 1)) = Worksheets(k).Cells(i, 1)
Summary.Cells(j + 2, 2 + Space * (k - 1)) = Worksheets(k).Cells(i, 10) * (9 / 5) + 32
Summary.Cells(j + 2, 3 + Space * (k - 1)) = Worksheets(k).Cells(i + 2, 16)
Summary.Cells(j + 2, 4 + Space * (k - 1)) = Worksheets(k).Cells(i + 1, 17)
j = j + 1
End If
Next i
Next k
For k = 1 To nSheet
For i = 8 To nRow
For j = 1 To (nSheet * Space)
If Round(Summary.Cells(i, 2 + Space * (k - 1)), 1) >= T_critical Then
Summary.Cells(i, 2 + Space * (k - 1)).Interior.ColorIndex = 36
End If
Next j
Next i
Next k
msg = "All Inlet Temperatures That Exceed Desired Maximum Inlet Temperature Have Been Highlighted."
MsgBox msg
End Sub