Error 1004 help

[Carl Brehm]

The range statement returns error 1004. Why?

Private Sub UpdateOnhand()
Dim X As Integer
Dim Y As Integer

X = 0
Y = 0

Do
Do
Range("ONHAND").Offset(X, Y).Value = Range("ONHAND").Offset(X,
Y).Value + Range("Received").Offset(X, Y).Value

Y = Y + 1

Loop Until Y = 119

X = X + 1

Loop Until X = 7

End Sub

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Cages

 

[Greg Wilson]

Your code returns this error because it tries to reference
a cell beyond the last column in the worksheet. If I
understand what you're trying to do, I think you just need
to rezero the Y variable after each inner loop as follows:

Private Sub UpdateOnhand()
Dim X As Integer
Dim Y As Integer
X = 0
Y = 0
Do
Do
Range("ONHAND").Offset(X, Y).Value = _
Range("ONHAND").Offset(X, Y).Value + _
Range("Received").Offset(X, Y).Value
Y = Y + 1
Loop Until Y = 119
Y = 0
X = X + 1
Loop Until X = 7
End Sub

Regards,
Greg

 

[Carl Brehm]

It does not even get through the loop once. Running the code with a break
set and stepping through it fails on the first pass when x & y =0.
--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Cages

 

[Dave Peterson]

See one reply at your other post.

The range statement returns error 1004. Why?

Private Sub UpdateOnhand()
Dim X As Integer
Dim Y As Integer

X = 0
Y = 0

Do
Do
Range("ONHAND").Offset(X, Y).Value = Range("ONHAND").Offset(X,
Y).Value + Range("Received").Offset(X, Y).Value

Y = Y + 1

Loop Until Y = 119

X = X + 1

Loop Until X = 7

End Sub

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Cages

 

[Greg Wilson]

Carl,

My understanding of your problem was that the inner loop
did not terminate when it was rerun by the outer loop the
second time. This is what should happen if you don't
rezero the Y variable after completion of each inner loop.

Note that your code increments the Y variable (column
offset) by 1 until it equals 119. Then the inner loop
terminates ("Loop Until Y = 119"). The outer loop then
causes a row offset downward and then reruns the inner
loop. However, Y equals 119 at the start of the second
run of the inner loop and is immediately incremented by 1
and therefore equals 120. At this point, the
statement "Loop Until Y = 119" will have no effect because
Y already exceeds 119 and continues to be incremented.
Therefore, the inner loop cannot self-terminate.
Consequentially, your code continues to reference one
column to the right until it attempts to reference beyond
the last column in the worksheet. This will cause Error
1004. If you run the code with the line that rezeros the
Y variable then this won't happen.

I assumed that the named ranges "ONHAND" and "Received"
were single cell ranges on the active sheet. I ran your
code after creating these named ranges and received the
Error 1004 message. I then ran it with the line that
rezeros the Y variable and it worked according to my
understanding of your intention.

Please advise if you have run the code with the line that
rezeros the Y variable and if the named ranges "ONHAND"
and "Received" refer to single cell ranges on the active
sheet. Also, since there are more than one version of
Error 1004 message, please advise of what else it says.

Regards,
Greg