Unknown address change

[Otto Moehrbach]

Excel 2002, WinXP
I have a simple 3 line code:

MsgBox j.Address 'A4, correct
j.Offset(, 3).Formula = "=E" & j.Row & "/C" & j.Row
MsgBox j.Address 'D4

"j" is a single cell range, A4. As you can see, "j" is set to D4 after the
formula is inserted into D4. Why? I expected "j" to stay A4.
Thanks for your help. Otto

 

[Bernie Deitrick]

Otto,

No matter what I do, I get A4 for both msgboxes. Have you dimensioned your
variables properly?

Dim j As Range
Set j = Range("A4")
MsgBox j.Address 'A4, correct
j.Offset(, 3).Formula = "=E" & j.Row & "/C" & j.Row
MsgBox j.Address 'D4

HTH,
Bernie
MS Excel MVP

 

[Bob Phillips]

Do you have any event code that could modify j?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Otto Moehrbach]

Bob
You nailed it. I didn't think my event macros would do anything in this
case. After you said that, I looked again. I'm running out of hair.
Thanks again. Otto

 

[Otto Moehrbach]

Bernie
Thanks for your response. Bob nailed it though. I had an event macro
that bit me. Otto

 

[Bob Phillips]

Otto,

It only occurred to me as I had some event code in my workbook that I tested
it on, and when I stepped through, it stepped into the event code -
light-bulb

Bob

 

[Otto Moehrbach]

That was good fortune. Otto

Otto,

It only occurred to me as I had some event code in my workbook that I
tested
it on, and when I stepped through, it stepped into the event code -
light-bulb

Bob