Saladsamurai
10-22-2009, 12:00 PM
It's the end of the day. I am toast and cannot figure out why this code keeps choking at the line in bold.
I keep getting the Error: "Object Variable or With Block Variable Not Set"
Sub Test()
Dim i As Long
Dim j As Long
Dim k As Integer
Dim nRow As Long
Dim nCol As Long
Dim nSheet As Integer
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim Summary As Worksheet
Dim ThisCell As String
Dim Dummy As Boolean
Dim Space As Integer
nSheet = 2
nRow = 250
nCol = 25
Space = 4
Dim CaseArray() As Variant
CaseArray = Array("BenchMark", "Cooler 1 - 14", "Cooler 1 - 3", "Cooler 1 - 8", _
"Cooler 2 - 10", "Cooler 2 - 4", "Cooler 3 - 12", "Cooler 3 - 3", "Cooler 3 - 8", _
"Cooler 4 - 12", "Cooler 4 - 5", "Cooler A - 14", "Cooler A - 3", "Cooler A - 8", _
"Cooler C - 12", "Cooler C - 3", "Cooler C - 8")
Set Summary = Worksheets("Summary")
Summary.Range("A3:FZ250").Delete
Summary.Range("A1:FZ250").Interior.ColorIndex = xlNone
For k = 1 To 5
Summary.Cells(j, 1 + Space * (k - 1)) = CaseArray(k - 1)
j = 3
For i = 2 To nRow
ThisCell = LTrim(Worksheets(k).Cells(i, 1))
If Left(ThisCell, 2) = "R/" Then
Summary.Cells(j, 1 + Space * (k - 1)) = Worksheets(k).Cells(i, 1)
Summary.Cells(j, 2 + Space * (k - 1)) = Worksheets(k).Cells(i, 10)
j = j + 1
End If
Next i
Next k
End Sub
I keep getting the Error: "Object Variable or With Block Variable Not Set"
Sub Test()
Dim i As Long
Dim j As Long
Dim k As Integer
Dim nRow As Long
Dim nCol As Long
Dim nSheet As Integer
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim Summary As Worksheet
Dim ThisCell As String
Dim Dummy As Boolean
Dim Space As Integer
nSheet = 2
nRow = 250
nCol = 25
Space = 4
Dim CaseArray() As Variant
CaseArray = Array("BenchMark", "Cooler 1 - 14", "Cooler 1 - 3", "Cooler 1 - 8", _
"Cooler 2 - 10", "Cooler 2 - 4", "Cooler 3 - 12", "Cooler 3 - 3", "Cooler 3 - 8", _
"Cooler 4 - 12", "Cooler 4 - 5", "Cooler A - 14", "Cooler A - 3", "Cooler A - 8", _
"Cooler C - 12", "Cooler C - 3", "Cooler C - 8")
Set Summary = Worksheets("Summary")
Summary.Range("A3:FZ250").Delete
Summary.Range("A1:FZ250").Interior.ColorIndex = xlNone
For k = 1 To 5
Summary.Cells(j, 1 + Space * (k - 1)) = CaseArray(k - 1)
j = 3
For i = 2 To nRow
ThisCell = LTrim(Worksheets(k).Cells(i, 1))
If Left(ThisCell, 2) = "R/" Then
Summary.Cells(j, 1 + Space * (k - 1)) = Worksheets(k).Cells(i, 1)
Summary.Cells(j, 2 + Space * (k - 1)) = Worksheets(k).Cells(i, 10)
j = j + 1
End If
Next i
Next k
End Sub