Unknown address change

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

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

"Otto Moehrbach" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

Do you have any event code that could modify j?

--

HTH

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


"Otto Moehrbach" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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
"Bob Phillips" <[email protected]> wrote in message
news:eq%[email protected]...
> Do you have any event code that could modify j?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Otto Moehrbach" <[email protected]> wrote in message
> news:[email protected]...
>> 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
Thanks for your response. Bob nailed it though. I had an event macro
that bit me. Otto
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:O%[email protected]...
> 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
>
> "Otto Moehrbach" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> "Bob Phillips" <[email protected]> wrote in message
> news:eq%[email protected]...
> > Do you have any event code that could modify j?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Otto Moehrbach" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

That was good fortune. Otto
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> "Bob Phillips" <[email protected]> wrote in message
>> news:eq%[email protected]...
>> > Do you have any event code that could modify j?
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Otto Moehrbach" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>