PDA

View Full Version : Trouble With Assigning Value to Cell



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

stanleydgrom
10-22-2009, 01:12 PM
Saladsamurai,

See my next post.

stanleydgrom
10-22-2009, 01:14 PM
Saladsamurai,

Try:




j = 3
For k = 1 To 5
Summary.Cells(j, 1 + Space * (k - 1)) = CaseArray(k - 1)

Zack Barresse
10-22-2009, 06:32 PM
I don't see where you define your "j" variable.

Saladsamurai
10-23-2009, 04:28 AM
Saladsamurai,

Try:




j = 3
For k = 1 To 5
Summary.Cells(j, 1 + Space * (k - 1)) = CaseArray(k - 1)



Okay! That's great. What a newb :)

The code runs now, BUT when I actually look at the worksheet "Summary" there is nothing there.

I inserted a break point after the first iteration of all loops. At this point
Summary.Cells(3, 1) shuold have the word "BenchMark" written to it, but does not.

Hmmm.... I might see the problem. I think in one of the inner loops, I overwrite it...

mdmackillop
10-23-2009, 05:26 AM
If you want us to test if something doen't work, you're best to submit a sample workbook.