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