Error writing value to cell
 

I want my VBA program to change the value of a cell. The cell
references are correct but the "write" or assignment step doesn't
work.

As a test, this line returns the correct cell location:

A = Worksheets("Sheet1").Cells(myLocRow(i) + Offset_Row, myLocCol(i) +
Offset_Col).Address

After the following test statement, variable A contains the current
value of the cell:

A = Worksheets("Sheet1").Cells(myLocRow(i) + Offset_Row, myLocCol(i) +
Offset_Col).Value

However, if I replace it with this step, something does not work
right:

Worksheets("Sheet1").Cells(myLocRow(i) + Offset_Row, myLocCol(i) +
Offset_Col).Value = "Test"

I added a temporary Stop statement immediately after the line above.
However, if I single-step through the above line, it does not reach
the Stop statement. Instead, excecution jumps to another step before
the assignment.

HELP! I have no idea what to do next.



--
Steven M - lid
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers

Error writing value to cell
 

Hi Steven,

May be check how you have declared your variables ...

HTH
Carim

Error writing value to cell
 

Hi!

Check if you are in Edit Mode, when you are tring to insert this
value...

Tomer

Error writing value to cell
 

The error must be before the assignment (which works OK for me) so can you
post some more code? What code causes the jump?

Try putting in the A= ..... immediately prior to the assigment - does this
work?

[Occassionally I have had problems where simply changing the order of
statements can "solve" an "error" .]

"Steven M (remove wax and invalid to repl" wrote:

I want my VBA program to change the value of a cell. The cell
references are correct but the "write" or assignment step doesn't
work.

As a test, this line returns the correct cell location:

A = Worksheets("Sheet1").Cells(myLocRow(i) + Offset_Row, myLocCol(i) +
Offset_Col).Address

After the following test statement, variable A contains the current
value of the cell:

A = Worksheets("Sheet1").Cells(myLocRow(i) + Offset_Row, myLocCol(i) +
Offset_Col).Value

However, if I replace it with this step, something does not work
right:

Worksheets("Sheet1").Cells(myLocRow(i) + Offset_Row, myLocCol(i) +
Offset_Col).Value = "Test"

I added a temporary Stop statement immediately after the line above.
However, if I single-step through the above line, it does not reach
the Stop statement. Instead, excecution jumps to another step before
the assignment.

HELP! I have no idea what to do next.



--
Steven M - lid
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers

Error writing value to cell
 

Je 9 Apr 2006 07:07:54 -0700, "Carim" skribis:

Hi Steven,

May be check how you have declared your variables ...

I don't understand. How should they be declared?

When I program in VBA for Word, I use "Option Explicit", but I don't
in Excel. Should I?



--
Steven M - lid
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers

Error writing value to cell
 

Je 9 Apr 2006 07:14:03 -0700, "Tomer" skribis:

Hi!

Check if you are in Edit Mode, when you are tring to insert this
value...

Sorry, I don't understand. What is Edit Mode and how do I switch to
it?

I have another short VBA program that changes the format of cells, and
I don't do anything special to get into "Edit Mode". Here it is

= = = = =

Sub CleanRange()
For Each Piece In Range("Table")
Piece.Select
Selection.Font.Bold = True
If Application.IsNumber(Piece) Then
Selection.Font.ColorIndex = xlAutomatic
Else
Selection.Font.ColorIndex = 3
End If
Next
End Sub

= = = = =

(I don't really have a question about the above code, it works the way
I want it.)



--
Steven M - lid
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers

Error writing value to cell
 

If the "A=" statement works, I doubt it is a variable type problem.

Also, I assume the variables have the same value as when the "A=" did work
(otherwise, the comparison to that statement would be irrelevant).

Do you have an error handler in effect. If so, when you say it doesn't reach
that statement, are you sent to the error handler? Perhaps disable the
error handler and see what the error is.

Also,

Is the worksheet protected?

If you go to Tools=Protection, is Unprotect Sheet an option?

If so, unprotect the sheet and try your code.

Other than that, I would expect it to work.

It is always wise to use Option Explicit.

--
Regards,
Tom Ogilvy



"Steven M (remove wax and invalid to reply)"
wrote in message ...
Je 9 Apr 2006 07:07:54 -0700, "Carim" skribis:

Hi Steven,

May be check how you have declared your variables ...

I don't understand. How should they be declared?

When I program in VBA for Word, I use "Option Explicit", but I don't
in Excel. Should I?



--
Steven M - lid
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers

Error writing value to cell
 

1. There is no error handler
2. Worksheet is not protected,
3. Have added Option Explicit.

I think I found a clue. Here's what VB Help says:

===
Cells Property
Range object: Returns a Range object that represents the cells in the
specified range (in other words, it does nothing). Read-only.
===

I suspect that this expression:

Worksheets("Sheet1").Cells(myLocRow(i) + Offset_Row, myLocCol(i) +
Offset_Col)

actually returns a range that contains a single cell, and not a single
cell.

Still not sure what to do next, but it's given me something to work
with.

Steven



Je Sun, 9 Apr 2006 11:48:10 -0400, "Tom Ogilvy"
skribis:

If the "A=" statement works, I doubt it is a variable type problem.

Also, I assume the variables have the same value as when the "A=" did work
(otherwise, the comparison to that statement would be irrelevant).

Do you have an error handler in effect. If so, when you say it doesn't reach
that statement, are you sent to the error handler? Perhaps disable the
error handler and see what the error is.

Also,

Is the worksheet protected?

If you go to Tools=Protection, is Unprotect Sheet an option?

If so, unprotect the sheet and try your code.

Other than that, I would expect it to work.

It is always wise to use Option Explicit.


--
Steven M - lid
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers

Error writing value to cell
 

Since you are having trouble stepping through the code, Try playing with
this code. It should be in a general module (insert=Module in the VBE)
rather than in a Worksheet Module or the ThisWorkbook module.

Option Explicit
Sub TestCode()
Dim rw As Long
Dim col As Long
Dim i As Long
Dim j As Long
Dim rng As Range
Dim A As Variant
Dim sh As Worksheet
Dim MyLocRow(1 To 2) As Long
Dim MyLocCol(1 To 2) As Long
Dim offset_Row As Long
Dim Offset_Col As Long

Set sh = Worksheets.Add(After:= _
Worksheets(Worksheets.Count))
With sh.Range("A1:T20")
.Formula = "=Char(column()+64)&row()"
.Formula = .Value
End With
For i = 1 To 2
MyLocRow(i) = Int(Rnd() * 10 + 1)
MyLocCol(i) = Int(Rnd() * 10 + 1)
Next
Offset_Col = Int(Rnd() * 10 + 1)
offset_Row = Int(Rnd() * 10 + 1)

For i = 1 To 2
For j = 1 To 2
rw = MyLocRow(i) + offset_Row
col = MyLocCol(j) + Offset_Col

If rw < 1 Or rw 65536 Then
MsgBox "Invalid row value: " & rw
Exit Sub
End If
If col < 1 Or col 256 Then
MsgBox "Invalid column value: " & col
Exit Sub
End If
On Error Resume Next
Set rng = sh.Cells(rw, col)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "rng was not set successfully"
Exit Sub
End If
A = rng.Value
rng.Value = "Test"

rng.Interior.ColorIndex = Int(Rnd() * 7 + 3)
MsgBox "For rng " & rng.Address(0, 0, xlA1, True) & _
vbNewLine & "Old Value was " & A & _
vbNewLine & "New value is " & rng.Value
Next j
Next i

End Sub


--
regards,
Tom Ogilvy


"Steven M (remove wax and invalid to reply)"
wrote in message ...
1. There is no error handler
2. Worksheet is not protected,
3. Have added Option Explicit.

I think I found a clue. Here's what VB Help says:

===
Cells Property
Range object: Returns a Range object that represents the cells in the
specified range (in other words, it does nothing). Read-only.
===

I suspect that this expression:

Worksheets("Sheet1").Cells(myLocRow(i) + Offset_Row, myLocCol(i) +
Offset_Col)

actually returns a range that contains a single cell, and not a single
cell.

Still not sure what to do next, but it's given me something to work
with.

Steven



Je Sun, 9 Apr 2006 11:48:10 -0400, "Tom Ogilvy"
skribis:

If the "A=" statement works, I doubt it is a variable type problem.

Also, I assume the variables have the same value as when the "A=" did
work
(otherwise, the comparison to that statement would be irrelevant).

Do you have an error handler in effect. If so, when you say it doesn't
reach
that statement, are you sent to the error handler? Perhaps disable the
error handler and see what the error is.

Also,

Is the worksheet protected?

If you go to Tools=Protection, is Unprotect Sheet an option?

If so, unprotect the sheet and try your code.

Other than that, I would expect it to work.

It is always wise to use Option Explicit.


--
Steven M - lid
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers

Error writing value to cell
 

Here's part of the latest version:

Dim A as Variant
Dim rng as Range

For i = 1 To 9
If myCount(i) = 1 Then
newCol = myLocCol(i) + Offset_Col
newRow = myLocRow(i) + Offset_Row
Set rng = Cells(newRow, newCol)
A = rng.Value
rng.Value = i
Stop
End If
Next i

When I execute it one step at a time, it executes "A = rng.Value" and
moves to the next statement, "rng.Value = i" When I press F8 at that
point, it moves immediately back to the "newCol =" statement, without
getting to the Stop statement at all.

The value of A after that assignment statement is correct - rng is
pointing to the correct cell and is reading the correct value. But
there's something about the "rng.Value = i" statement that is making
things go nuts.

The other parts of this subroutine are math manipulations. This is a
user-defined function that is called from other cells in the
spreadsheet.



Je Sun, 9 Apr 2006 13:10:16 -0400, "Tom Ogilvy"
skribis:


Since you are having trouble stepping through the code, Try playing with
this code. It should be in a general module (insert=Module in the VBE)
rather than in a Worksheet Module or the ThisWorkbook module.

Option Explicit
Sub TestCode()
Dim rw As Long
Dim col As Long
Dim i As Long
Dim j As Long
Dim rng As Range
Dim A As Variant
Dim sh As Worksheet
Dim MyLocRow(1 To 2) As Long
Dim MyLocCol(1 To 2) As Long
Dim offset_Row As Long
Dim Offset_Col As Long

Set sh = Worksheets.Add(After:= _
Worksheets(Worksheets.Count))
With sh.Range("A1:T20")
.Formula = "=Char(column()+64)&row()"
.Formula = .Value
End With
For i = 1 To 2
MyLocRow(i) = Int(Rnd() * 10 + 1)
MyLocCol(i) = Int(Rnd() * 10 + 1)
Next
Offset_Col = Int(Rnd() * 10 + 1)
offset_Row = Int(Rnd() * 10 + 1)

For i = 1 To 2
For j = 1 To 2
rw = MyLocRow(i) + offset_Row
col = MyLocCol(j) + Offset_Col

If rw < 1 Or rw 65536 Then
MsgBox "Invalid row value: " & rw
Exit Sub
End If
If col < 1 Or col 256 Then
MsgBox "Invalid column value: " & col
Exit Sub
End If
On Error Resume Next
Set rng = sh.Cells(rw, col)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "rng was not set successfully"
Exit Sub
End If
A = rng.Value
rng.Value = "Test"

rng.Interior.ColorIndex = Int(Rnd() * 7 + 3)
MsgBox "For rng " & rng.Address(0, 0, xlA1, True) & _
vbNewLine & "Old Value was " & A & _
vbNewLine & "New value is " & rng.Value
Next j
Next i

End Sub


--
Steven M - lid
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers

Error writing value to cell
 

A user defined function called by a worksheet cell can not change the value
of any cell or alter the environment in any way. It can only return a value.
(it can read other values and so forth, but can't change anything that could
be visible).



--
Regards,
Tom Ogilvy



"Steven M (remove wax and invalid to repl" wrote:

Here's part of the latest version:

Dim A as Variant
Dim rng as Range

For i = 1 To 9
If myCount(i) = 1 Then
newCol = myLocCol(i) + Offset_Col
newRow = myLocRow(i) + Offset_Row
Set rng = Cells(newRow, newCol)
A = rng.Value
rng.Value = i
Stop
End If
Next i

When I execute it one step at a time, it executes "A = rng.Value" and
moves to the next statement, "rng.Value = i" When I press F8 at that
point, it moves immediately back to the "newCol =" statement, without
getting to the Stop statement at all.

The value of A after that assignment statement is correct - rng is
pointing to the correct cell and is reading the correct value. But
there's something about the "rng.Value = i" statement that is making
things go nuts.

The other parts of this subroutine are math manipulations. This is a
user-defined function that is called from other cells in the
spreadsheet.



Je Sun, 9 Apr 2006 13:10:16 -0400, "Tom Ogilvy"
skribis:


Since you are having trouble stepping through the code, Try playing with
this code. It should be in a general module (insert=Module in the VBE)
rather than in a Worksheet Module or the ThisWorkbook module.

Option Explicit
Sub TestCode()
Dim rw As Long
Dim col As Long
Dim i As Long
Dim j As Long
Dim rng As Range
Dim A As Variant
Dim sh As Worksheet
Dim MyLocRow(1 To 2) As Long
Dim MyLocCol(1 To 2) As Long
Dim offset_Row As Long
Dim Offset_Col As Long

Set sh = Worksheets.Add(After:= _
Worksheets(Worksheets.Count))
With sh.Range("A1:T20")
.Formula = "=Char(column()+64)&row()"
.Formula = .Value
End With
For i = 1 To 2
MyLocRow(i) = Int(Rnd() * 10 + 1)
MyLocCol(i) = Int(Rnd() * 10 + 1)
Next
Offset_Col = Int(Rnd() * 10 + 1)
offset_Row = Int(Rnd() * 10 + 1)

For i = 1 To 2
For j = 1 To 2
rw = MyLocRow(i) + offset_Row
col = MyLocCol(j) + Offset_Col

If rw < 1 Or rw 65536 Then
MsgBox "Invalid row value: " & rw
Exit Sub
End If
If col < 1 Or col 256 Then
MsgBox "Invalid column value: " & col
Exit Sub
End If
On Error Resume Next
Set rng = sh.Cells(rw, col)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "rng was not set successfully"
Exit Sub
End If
A = rng.Value
rng.Value = "Test"

rng.Interior.ColorIndex = Int(Rnd() * 7 + 3)
MsgBox "For rng " & rng.Address(0, 0, xlA1, True) & _
vbNewLine & "Old Value was " & A & _
vbNewLine & "New value is " & rng.Value
Next j
Next i

End Sub


--
Steven M - lid
(remove wax and invalid to reply)

A fool and his money are soon elected. -- Will Rogers