Relative, Offset, ActiveCell
 

I'm an Access Programmer using automation to send data to 72 different Excel
cells.
These 72 cells all have the same relative cell "structure"...IE, the Names
below are
....
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have the ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value) 9000) Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value - Range("CNine").Value)
End If
End If
End Sub

Relative, Offset, ActiveCell
 

Just to clarify -

If Target = Range("DNine")

Do you intend that line to mean

If the Value in the cell that's just changed is the same as the value in
DNine Then
or
If the cell that's just changed is the DNine cell Then

As written, the code does the former but somehow I suspect you mean the
latter

Regards,
Peter T



"Bob Barnes" wrote in message
...
I'm an Access Programmer using automation to send data to 72 different
Excel
cells.
These 72 cells all have the same relative cell "structure"...IE, the Names
below are
...
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have the ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value) 9000) Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value - Range("CNine").Value)
End If
End If
End Sub

Relative, Offset, ActiveCell
 

Yes...the value would change in Cell "DNine"...then compare a "previous"
Reading in Cell "CNine" to place a calculated result in Cell "C_D_Nine".

This compares meter readings, and if a meter "turns over" from 9999 to 0000..
then...
If Cell "DNine" = 7, and Cell "CNine" = 9997, then Cell "C_D_Nine" = 11.
....or..
If Cell "DNine" = 7879, and Cell "CNine" = 7863, then Cell "C_D_Nine" = 16.

TIA - Bob

"Peter T" wrote:

Just to clarify -

If Target = Range("DNine")

Do you intend that line to mean

If the Value in the cell that's just changed is the same as the value in
DNine Then
or
If the cell that's just changed is the DNine cell Then

As written, the code does the former but somehow I suspect you mean the
latter

Regards,
Peter T



"Bob Barnes" wrote in message
...
I'm an Access Programmer using automation to send data to 72 different
Excel
cells.
These 72 cells all have the same relative cell "structure"...IE, the Names
below are
...
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have the ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value) 9000) Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value - Range("CNine").Value)
End If
End If
End Sub

Relative, Offset, ActiveCell
 

Start with something simple, in a test workbook on a sheet with the named
cell. Note you'll need to initiate by storing the value of your DNine cell,
in the example simply by swtiching sheets and back.

Private mOldDNine As Variant
Private Sub Worksheet_Activate()
'switch to another sheet and back to initiate
mOldDNine = Range("DNine")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vNewDNine
If Not Intersect(Target, Range("DNine")) Is Nothing Then
vNewDNine = Range("DNine").Value
MsgBox "old DNine value : " & mOldDNine & vbCr & "new DNine : " &
vNewDNine
mOldDNine = vNewDNine

End If
End Sub


Post back if not clear what code after "IF" the DNine cell changes. Don't
post any code, just explain one or two examples with details what you have
and what you want to do.

Regards,
Peter T


"Bob Barnes" wrote in message
...
Yes...the value would change in Cell "DNine"...then compare a "previous"
Reading in Cell "CNine" to place a calculated result in Cell "C_D_Nine".

This compares meter readings, and if a meter "turns over" from 9999 to
0000..
then...
If Cell "DNine" = 7, and Cell "CNine" = 9997, then Cell "C_D_Nine" = 11.
...or..
If Cell "DNine" = 7879, and Cell "CNine" = 7863, then Cell "C_D_Nine" =
16.

TIA - Bob

"Peter T" wrote:

Just to clarify -

If Target = Range("DNine")

Do you intend that line to mean

If the Value in the cell that's just changed is the same as the value in
DNine Then
or
If the cell that's just changed is the DNine cell Then

As written, the code does the former but somehow I suspect you mean the
latter

Regards,
Peter T



"Bob Barnes" wrote in message
...
I'm an Access Programmer using automation to send data to 72 different
Excel
cells.
These 72 cells all have the same relative cell "structure"...IE, the
Names
below are
...
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have the ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value) 9000) Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value -
Range("CNine").Value)
End If
End If
End Sub

Relative, Offset, ActiveCell
 

Thank you.

Isn't there a R0C-1...or something like that?

I Programmed Lotus 1-2-3 years ago using their @Cell & @Cellpointer, but
switched to Access in 1995.

Shouldn't people be able to use "ActiveCell", or something like that..???

"Peter T" wrote:

Start with something simple, in a test workbook on a sheet with the named
cell. Note you'll need to initiate by storing the value of your DNine cell,
in the example simply by swtiching sheets and back.

Private mOldDNine As Variant
Private Sub Worksheet_Activate()
'switch to another sheet and back to initiate
mOldDNine = Range("DNine")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vNewDNine
If Not Intersect(Target, Range("DNine")) Is Nothing Then
vNewDNine = Range("DNine").Value
MsgBox "old DNine value : " & mOldDNine & vbCr & "new DNine : " &
vNewDNine
mOldDNine = vNewDNine

End If
End Sub


Post back if not clear what code after "IF" the DNine cell changes. Don't
post any code, just explain one or two examples with details what you have
and what you want to do.

Regards,
Peter T


"Bob Barnes" wrote in message
...
Yes...the value would change in Cell "DNine"...then compare a "previous"
Reading in Cell "CNine" to place a calculated result in Cell "C_D_Nine".

This compares meter readings, and if a meter "turns over" from 9999 to
0000..
then...
If Cell "DNine" = 7, and Cell "CNine" = 9997, then Cell "C_D_Nine" = 11.
...or..
If Cell "DNine" = 7879, and Cell "CNine" = 7863, then Cell "C_D_Nine" =
16.

TIA - Bob

"Peter T" wrote:

Just to clarify -

If Target = Range("DNine")

Do you intend that line to mean

If the Value in the cell that's just changed is the same as the value in
DNine Then
or
If the cell that's just changed is the DNine cell Then

As written, the code does the former but somehow I suspect you mean the
latter

Regards,
Peter T



"Bob Barnes" wrote in message
...
I'm an Access Programmer using automation to send data to 72 different
Excel
cells.
These 72 cells all have the same relative cell "structure"...IE, the
Names
below are
...
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have the ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value) 9000) Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value -
Range("CNine").Value)
End If
End If
End Sub

Relative, Offset, ActiveCell
 

The relative addresses, which I've never used, should work...because the
Worksheet_Change would examine the Cell "triggering" the Event...then look
over one Column to the left, in the same row, run the code, & place the
result in the same Column, down 2 Rows.

I would also run this on Workbook_Open, selecting the 72 Cells,
individually, by...
Range("DNine").Select --- that would be the ActiveCell..
and Calling the Subroutine to get the resulting value.

...or I could replicate the code, w/ the Names 72 times...it would work, and
shouldn't take long, but why do that...when a Selection of the 72 Cells, one
at a time, then calling the Subroutine w/ relative cell adddresses would
work...or am I missing something?

TIA - Bob

"Peter T" wrote:

What would you want ROC-1 for

Sure there's ActiveCell, but that's not necessarily the cell(s) that
has/have just triggered a change event.

Regards,
Peter T

"Bob Barnes" wrote in message
...
Thank you.

Isn't there a R0C-1...or something like that?

I Programmed Lotus 1-2-3 years ago using their @Cell & @Cellpointer, but
switched to Access in 1995.

Shouldn't people be able to use "ActiveCell", or something like that..???

"Peter T" wrote:

Start with something simple, in a test workbook on a sheet with the named
cell. Note you'll need to initiate by storing the value of your DNine
cell,
in the example simply by swtiching sheets and back.

Private mOldDNine As Variant
Private Sub Worksheet_Activate()
'switch to another sheet and back to initiate
mOldDNine = Range("DNine")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vNewDNine
If Not Intersect(Target, Range("DNine")) Is Nothing Then
vNewDNine = Range("DNine").Value
MsgBox "old DNine value : " & mOldDNine & vbCr & "new DNine : " &
vNewDNine
mOldDNine = vNewDNine

End If
End Sub


Post back if not clear what code after "IF" the DNine cell changes. Don't
post any code, just explain one or two examples with details what you
have
and what you want to do.

Regards,
Peter T


"Bob Barnes" wrote in message
...
Yes...the value would change in Cell "DNine"...then compare a
"previous"
Reading in Cell "CNine" to place a calculated result in Cell
"C_D_Nine".

This compares meter readings, and if a meter "turns over" from 9999 to
0000..
then...
If Cell "DNine" = 7, and Cell "CNine" = 9997, then Cell "C_D_Nine" =
11.
...or..
If Cell "DNine" = 7879, and Cell "CNine" = 7863, then Cell "C_D_Nine" =
16.

TIA - Bob

"Peter T" wrote:

Just to clarify -

If Target = Range("DNine")

Do you intend that line to mean

If the Value in the cell that's just changed is the same as the value
in
DNine Then
or
If the cell that's just changed is the DNine cell Then

As written, the code does the former but somehow I suspect you mean
the
latter

Regards,
Peter T



"Bob Barnes" wrote in message
...
I'm an Access Programmer using automation to send data to 72
different
Excel
cells.
These 72 cells all have the same relative cell "structure"...IE, the
Names
below are
...
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have the
ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value) 9000) Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value -
Range("CNine").Value)
End If
End If
End Sub

Relative, Offset, ActiveCell
 

To refer to a cell "relative" to a given cell use the Offset function (see
help)
Afraid I don't follow at all the rest of what you are trying to do.

Regards,
Peter T

"Bob Barnes" wrote in message
...
The relative addresses, which I've never used, should work...because the
Worksheet_Change would examine the Cell "triggering" the Event...then look
over one Column to the left, in the same row, run the code, & place the
result in the same Column, down 2 Rows.

I would also run this on Workbook_Open, selecting the 72 Cells,
individually, by...
Range("DNine").Select --- that would be the ActiveCell..
and Calling the Subroutine to get the resulting value.

..or I could replicate the code, w/ the Names 72 times...it would work,
and
shouldn't take long, but why do that...when a Selection of the 72 Cells,
one
at a time, then calling the Subroutine w/ relative cell adddresses would
work...or am I missing something?

TIA - Bob

"Peter T" wrote:

What would you want ROC-1 for

Sure there's ActiveCell, but that's not necessarily the cell(s) that
has/have just triggered a change event.

Regards,
Peter T

"Bob Barnes" wrote in message
...
Thank you.

Isn't there a R0C-1...or something like that?

I Programmed Lotus 1-2-3 years ago using their @Cell & @Cellpointer,
but
switched to Access in 1995.

Shouldn't people be able to use "ActiveCell", or something like
that..???

"Peter T" wrote:

Start with something simple, in a test workbook on a sheet with the
named
cell. Note you'll need to initiate by storing the value of your DNine
cell,
in the example simply by swtiching sheets and back.

Private mOldDNine As Variant
Private Sub Worksheet_Activate()
'switch to another sheet and back to initiate
mOldDNine = Range("DNine")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vNewDNine
If Not Intersect(Target, Range("DNine")) Is Nothing Then
vNewDNine = Range("DNine").Value
MsgBox "old DNine value : " & mOldDNine & vbCr & "new DNine :
" &
vNewDNine
mOldDNine = vNewDNine

End If
End Sub


Post back if not clear what code after "IF" the DNine cell changes.
Don't
post any code, just explain one or two examples with details what you
have
and what you want to do.

Regards,
Peter T


"Bob Barnes" wrote in message
...
Yes...the value would change in Cell "DNine"...then compare a
"previous"
Reading in Cell "CNine" to place a calculated result in Cell
"C_D_Nine".

This compares meter readings, and if a meter "turns over" from 9999
to
0000..
then...
If Cell "DNine" = 7, and Cell "CNine" = 9997, then Cell "C_D_Nine" =
11.
...or..
If Cell "DNine" = 7879, and Cell "CNine" = 7863, then Cell
"C_D_Nine" =
16.

TIA - Bob

"Peter T" wrote:

Just to clarify -

If Target = Range("DNine")

Do you intend that line to mean

If the Value in the cell that's just changed is the same as the
value
in
DNine Then
or
If the cell that's just changed is the DNine cell Then

As written, the code does the former but somehow I suspect you mean
the
latter

Regards,
Peter T



"Bob Barnes" wrote in message
...
I'm an Access Programmer using automation to send data to 72
different
Excel
cells.
These 72 cells all have the same relative cell "structure"...IE,
the
Names
below are
...
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have the
ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value) 9000) Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value -
Range("CNine").Value)
End If
End If
End Sub

Relative, Offset, ActiveCell
 

I'll check Offset. The Macro Recorder used to (I even found how it's
supposed to still record relative addresses, but I couldn't get it to work).
I'll try this.

Thank you - Bob

"Peter T" wrote:

To refer to a cell "relative" to a given cell use the Offset function (see
help)
Afraid I don't follow at all the rest of what you are trying to do.

Regards,
Peter T

"Bob Barnes" wrote in message
...
The relative addresses, which I've never used, should work...because the
Worksheet_Change would examine the Cell "triggering" the Event...then look
over one Column to the left, in the same row, run the code, & place the
result in the same Column, down 2 Rows.

I would also run this on Workbook_Open, selecting the 72 Cells,
individually, by...
Range("DNine").Select --- that would be the ActiveCell..
and Calling the Subroutine to get the resulting value.

..or I could replicate the code, w/ the Names 72 times...it would work,
and
shouldn't take long, but why do that...when a Selection of the 72 Cells,
one
at a time, then calling the Subroutine w/ relative cell adddresses would
work...or am I missing something?

TIA - Bob

"Peter T" wrote:

What would you want ROC-1 for

Sure there's ActiveCell, but that's not necessarily the cell(s) that
has/have just triggered a change event.

Regards,
Peter T

"Bob Barnes" wrote in message
...
Thank you.

Isn't there a R0C-1...or something like that?

I Programmed Lotus 1-2-3 years ago using their @Cell & @Cellpointer,
but
switched to Access in 1995.

Shouldn't people be able to use "ActiveCell", or something like
that..???

"Peter T" wrote:

Start with something simple, in a test workbook on a sheet with the
named
cell. Note you'll need to initiate by storing the value of your DNine
cell,
in the example simply by swtiching sheets and back.

Private mOldDNine As Variant
Private Sub Worksheet_Activate()
'switch to another sheet and back to initiate
mOldDNine = Range("DNine")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vNewDNine
If Not Intersect(Target, Range("DNine")) Is Nothing Then
vNewDNine = Range("DNine").Value
MsgBox "old DNine value : " & mOldDNine & vbCr & "new DNine :
" &
vNewDNine
mOldDNine = vNewDNine

End If
End Sub


Post back if not clear what code after "IF" the DNine cell changes.
Don't
post any code, just explain one or two examples with details what you
have
and what you want to do.

Regards,
Peter T


"Bob Barnes" wrote in message
...
Yes...the value would change in Cell "DNine"...then compare a
"previous"
Reading in Cell "CNine" to place a calculated result in Cell
"C_D_Nine".

This compares meter readings, and if a meter "turns over" from 9999
to
0000..
then...
If Cell "DNine" = 7, and Cell "CNine" = 9997, then Cell "C_D_Nine" =
11.
...or..
If Cell "DNine" = 7879, and Cell "CNine" = 7863, then Cell
"C_D_Nine" =
16.

TIA - Bob

"Peter T" wrote:

Just to clarify -

If Target = Range("DNine")

Do you intend that line to mean

If the Value in the cell that's just changed is the same as the
value
in
DNine Then
or
If the cell that's just changed is the DNine cell Then

As written, the code does the former but somehow I suspect you mean
the
latter

Regards,
Peter T



"Bob Barnes" wrote in message
...
I'm an Access Programmer using automation to send data to 72
different
Excel
cells.
These 72 cells all have the same relative cell "structure"...IE,
the
Names
below are
...
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have the
ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value) 9000) Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value -
Range("CNine").Value)
End If
End If
End Sub

Relative, Offset, ActiveCell
 

The macro recorder still does have the option to record with
relative/absolute addresses, but I doubt that's what you really want.
Difficult to say as you haven't explained what you are after accomplishing

Regards,
Peter T


"Bob Barnes" wrote in message
...
I'll check Offset. The Macro Recorder used to (I even found how it's
supposed to still record relative addresses, but I couldn't get it to
work).
I'll try this.

Thank you - Bob

"Peter T" wrote:

To refer to a cell "relative" to a given cell use the Offset function
(see
help)
Afraid I don't follow at all the rest of what you are trying to do.

Regards,
Peter T

"Bob Barnes" wrote in message
...
The relative addresses, which I've never used, should work...because
the
Worksheet_Change would examine the Cell "triggering" the Event...then
look
over one Column to the left, in the same row, run the code, & place the
result in the same Column, down 2 Rows.

I would also run this on Workbook_Open, selecting the 72 Cells,
individually, by...
Range("DNine").Select --- that would be the ActiveCell..
and Calling the Subroutine to get the resulting value.

..or I could replicate the code, w/ the Names 72 times...it would work,
and
shouldn't take long, but why do that...when a Selection of the 72
Cells,
one
at a time, then calling the Subroutine w/ relative cell adddresses
would
work...or am I missing something?

TIA - Bob

"Peter T" wrote:

What would you want ROC-1 for

Sure there's ActiveCell, but that's not necessarily the cell(s) that
has/have just triggered a change event.

Regards,
Peter T

"Bob Barnes" wrote in message
...
Thank you.

Isn't there a R0C-1...or something like that?

I Programmed Lotus 1-2-3 years ago using their @Cell & @Cellpointer,
but
switched to Access in 1995.

Shouldn't people be able to use "ActiveCell", or something like
that..???

"Peter T" wrote:

Start with something simple, in a test workbook on a sheet with the
named
cell. Note you'll need to initiate by storing the value of your
DNine
cell,
in the example simply by swtiching sheets and back.

Private mOldDNine As Variant
Private Sub Worksheet_Activate()
'switch to another sheet and back to initiate
mOldDNine = Range("DNine")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vNewDNine
If Not Intersect(Target, Range("DNine")) Is Nothing Then
vNewDNine = Range("DNine").Value
MsgBox "old DNine value : " & mOldDNine & vbCr & "new DNine
:
" &
vNewDNine
mOldDNine = vNewDNine

End If
End Sub


Post back if not clear what code after "IF" the DNine cell changes.
Don't
post any code, just explain one or two examples with details what
you
have
and what you want to do.

Regards,
Peter T


"Bob Barnes" wrote in message
...
Yes...the value would change in Cell "DNine"...then compare a
"previous"
Reading in Cell "CNine" to place a calculated result in Cell
"C_D_Nine".

This compares meter readings, and if a meter "turns over" from
9999
to
0000..
then...
If Cell "DNine" = 7, and Cell "CNine" = 9997, then Cell
"C_D_Nine" =
11.
...or..
If Cell "DNine" = 7879, and Cell "CNine" = 7863, then Cell
"C_D_Nine" =
16.

TIA - Bob

"Peter T" wrote:

Just to clarify -

If Target = Range("DNine")

Do you intend that line to mean

If the Value in the cell that's just changed is the same as the
value
in
DNine Then
or
If the cell that's just changed is the DNine cell Then

As written, the code does the former but somehow I suspect you
mean
the
latter

Regards,
Peter T



"Bob Barnes" wrote in
message
...
I'm an Access Programmer using automation to send data to 72
different
Excel
cells.
These 72 cells all have the same relative cell
"structure"...IE,
the
Names
below are
...
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have the
ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value) 9000)
Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value -
Range("CNine").Value)
End If
End If
End Sub

Relative, Offset, ActiveCell
 

Just looking for something like...but, I don't know the proper syntax...
Does this help? TIA.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("ActiveCell") Then
If ((Range("ActiveCell").Value - Range("C-1R0").Value < 0) _
And Abs(Range("ActiveCell").Value - Range("C-1R0").Value) 9000)
Then
If Len(Range("C-1R0")) = 4 Then
I = (10000 - Range("C-1R0").Value)
ElseIf Len(Range("C-1R0")) = 5 Then
I = (100000 - Range("C-1R0").Value)
ElseIf Len(Range("C-1R0")) = 6 Then
I = (1000000 - Range("C-1R0").Value)
ElseIf Len(Range("C-1R0")) = 7 Then
I = (10000000 - Range("C-1R0").Value)
End If
Range("C0R2").Value = Range("ActiveCell").Value + I
Else
Range("C0R2").Value = (Range("ActiveCell").Value -
Range("C-1R0").Value)
End If
End If
End Sub

"Peter T" wrote:

The macro recorder still does have the option to record with
relative/absolute addresses, but I doubt that's what you really want.
Difficult to say as you haven't explained what you are after accomplishing

Regards,
Peter T


"Bob Barnes" wrote in message
...
I'll check Offset. The Macro Recorder used to (I even found how it's
supposed to still record relative addresses, but I couldn't get it to
work).
I'll try this.

Thank you - Bob

"Peter T" wrote:

To refer to a cell "relative" to a given cell use the Offset function
(see
help)
Afraid I don't follow at all the rest of what you are trying to do.

Regards,
Peter T

"Bob Barnes" wrote in message
...
The relative addresses, which I've never used, should work...because
the
Worksheet_Change would examine the Cell "triggering" the Event...then
look
over one Column to the left, in the same row, run the code, & place the
result in the same Column, down 2 Rows.

I would also run this on Workbook_Open, selecting the 72 Cells,
individually, by...
Range("DNine").Select --- that would be the ActiveCell..
and Calling the Subroutine to get the resulting value.

..or I could replicate the code, w/ the Names 72 times...it would work,
and
shouldn't take long, but why do that...when a Selection of the 72
Cells,
one
at a time, then calling the Subroutine w/ relative cell adddresses
would
work...or am I missing something?

TIA - Bob

"Peter T" wrote:

What would you want ROC-1 for

Sure there's ActiveCell, but that's not necessarily the cell(s) that
has/have just triggered a change event.

Regards,
Peter T

"Bob Barnes" wrote in message
...
Thank you.

Isn't there a R0C-1...or something like that?

I Programmed Lotus 1-2-3 years ago using their @Cell & @Cellpointer,
but
switched to Access in 1995.

Shouldn't people be able to use "ActiveCell", or something like
that..???

"Peter T" wrote:

Start with something simple, in a test workbook on a sheet with the
named
cell. Note you'll need to initiate by storing the value of your
DNine
cell,
in the example simply by swtiching sheets and back.

Private mOldDNine As Variant
Private Sub Worksheet_Activate()
'switch to another sheet and back to initiate
mOldDNine = Range("DNine")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vNewDNine
If Not Intersect(Target, Range("DNine")) Is Nothing Then
vNewDNine = Range("DNine").Value
MsgBox "old DNine value : " & mOldDNine & vbCr & "new DNine
:
" &
vNewDNine
mOldDNine = vNewDNine

End If
End Sub


Post back if not clear what code after "IF" the DNine cell changes.
Don't
post any code, just explain one or two examples with details what
you
have
and what you want to do.

Regards,
Peter T


"Bob Barnes" wrote in message
...
Yes...the value would change in Cell "DNine"...then compare a
"previous"
Reading in Cell "CNine" to place a calculated result in Cell
"C_D_Nine".

This compares meter readings, and if a meter "turns over" from
9999
to
0000..
then...
If Cell "DNine" = 7, and Cell "CNine" = 9997, then Cell
"C_D_Nine" =
11.
...or..
If Cell "DNine" = 7879, and Cell "CNine" = 7863, then Cell
"C_D_Nine" =
16.

TIA - Bob

"Peter T" wrote:

Just to clarify -

If Target = Range("DNine")

Do you intend that line to mean

If the Value in the cell that's just changed is the same as the
value
in
DNine Then
or
If the cell that's just changed is the DNine cell Then

As written, the code does the former but somehow I suspect you
mean
the
latter

Regards,
Peter T



"Bob Barnes" wrote in
message
...
I'm an Access Programmer using automation to send data to 72
different
Excel
cells.
These 72 cells all have the same relative cell
"structure"...IE,
the
Names
below are
...
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have the
ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value) 9000)
Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value -
Range("CNine").Value)
End If
End If
End Sub

Relative, Offset, ActiveCell
 

Unfortunately there are too many ways of second guessing what the code is
supposed to do, starting with the first line. As I suggested earlier try and
explain without code. Did you try the example I posted?

Regards,
Peter T

"Bob Barnes" wrote in message
...
Just looking for something like...but, I don't know the proper syntax...
Does this help? TIA.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("ActiveCell") Then
If ((Range("ActiveCell").Value - Range("C-1R0").Value < 0) _
And Abs(Range("ActiveCell").Value - Range("C-1R0").Value) 9000)
Then
If Len(Range("C-1R0")) = 4 Then
I = (10000 - Range("C-1R0").Value)
ElseIf Len(Range("C-1R0")) = 5 Then
I = (100000 - Range("C-1R0").Value)
ElseIf Len(Range("C-1R0")) = 6 Then
I = (1000000 - Range("C-1R0").Value)
ElseIf Len(Range("C-1R0")) = 7 Then
I = (10000000 - Range("C-1R0").Value)
End If
Range("C0R2").Value = Range("ActiveCell").Value + I
Else
Range("C0R2").Value = (Range("ActiveCell").Value -
Range("C-1R0").Value)
End If
End If
End Sub

"Peter T" wrote:

The macro recorder still does have the option to record with
relative/absolute addresses, but I doubt that's what you really want.
Difficult to say as you haven't explained what you are after
accomplishing

Regards,
Peter T


"Bob Barnes" wrote in message
...
I'll check Offset. The Macro Recorder used to (I even found how it's
supposed to still record relative addresses, but I couldn't get it to
work).
I'll try this.

Thank you - Bob

"Peter T" wrote:

To refer to a cell "relative" to a given cell use the Offset function
(see
help)
Afraid I don't follow at all the rest of what you are trying to do.

Regards,
Peter T

"Bob Barnes" wrote in message
...
The relative addresses, which I've never used, should work...because
the
Worksheet_Change would examine the Cell "triggering" the
Event...then
look
over one Column to the left, in the same row, run the code, & place
the
result in the same Column, down 2 Rows.

I would also run this on Workbook_Open, selecting the 72 Cells,
individually, by...
Range("DNine").Select --- that would be the ActiveCell..
and Calling the Subroutine to get the resulting value.

..or I could replicate the code, w/ the Names 72 times...it would
work,
and
shouldn't take long, but why do that...when a Selection of the 72
Cells,
one
at a time, then calling the Subroutine w/ relative cell adddresses
would
work...or am I missing something?

TIA - Bob

"Peter T" wrote:

What would you want ROC-1 for

Sure there's ActiveCell, but that's not necessarily the cell(s)
that
has/have just triggered a change event.

Regards,
Peter T

"Bob Barnes" wrote in message
...
Thank you.

Isn't there a R0C-1...or something like that?

I Programmed Lotus 1-2-3 years ago using their @Cell &
@Cellpointer,
but
switched to Access in 1995.

Shouldn't people be able to use "ActiveCell", or something like
that..???

"Peter T" wrote:

Start with something simple, in a test workbook on a sheet with
the
named
cell. Note you'll need to initiate by storing the value of your
DNine
cell,
in the example simply by swtiching sheets and back.

Private mOldDNine As Variant
Private Sub Worksheet_Activate()
'switch to another sheet and back to initiate
mOldDNine = Range("DNine")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vNewDNine
If Not Intersect(Target, Range("DNine")) Is Nothing Then
vNewDNine = Range("DNine").Value
MsgBox "old DNine value : " & mOldDNine & vbCr & "new
DNine
:
" &
vNewDNine
mOldDNine = vNewDNine

End If
End Sub


Post back if not clear what code after "IF" the DNine cell
changes.
Don't
post any code, just explain one or two examples with details
what
you
have
and what you want to do.

Regards,
Peter T


"Bob Barnes" wrote in
message
...
Yes...the value would change in Cell "DNine"...then compare a
"previous"
Reading in Cell "CNine" to place a calculated result in Cell
"C_D_Nine".

This compares meter readings, and if a meter "turns over" from
9999
to
0000..
then...
If Cell "DNine" = 7, and Cell "CNine" = 9997, then Cell
"C_D_Nine" =
11.
...or..
If Cell "DNine" = 7879, and Cell "CNine" = 7863, then Cell
"C_D_Nine" =
16.

TIA - Bob

"Peter T" wrote:

Just to clarify -

If Target = Range("DNine")

Do you intend that line to mean

If the Value in the cell that's just changed is the same as
the
value
in
DNine Then
or
If the cell that's just changed is the DNine cell Then

As written, the code does the former but somehow I suspect
you
mean
the
latter

Regards,
Peter T



"Bob Barnes" wrote in
message
...
I'm an Access Programmer using automation to send data to
72
different
Excel
cells.
These 72 cells all have the same relative cell
"structure"...IE,
the
Names
below are
...
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have
the
ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value)
9000)
Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value -
Range("CNine").Value)
End If
End If
End Sub

Relative, Offset, ActiveCell
 

Peter T - thank you...I opened another thread called "Another Try - Calculate
from ActiveCell...Progresss did occur, but I still have an issue (see my
latest Post there).

TIA - Bob

"Peter T" wrote:

Unfortunately there are too many ways of second guessing what the code is
supposed to do, starting with the first line. As I suggested earlier try and
explain without code. Did you try the example I posted?

Regards,
Peter T

"Bob Barnes" wrote in message
...
Just looking for something like...but, I don't know the proper syntax...
Does this help? TIA.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("ActiveCell") Then
If ((Range("ActiveCell").Value - Range("C-1R0").Value < 0) _
And Abs(Range("ActiveCell").Value - Range("C-1R0").Value) 9000)
Then
If Len(Range("C-1R0")) = 4 Then
I = (10000 - Range("C-1R0").Value)
ElseIf Len(Range("C-1R0")) = 5 Then
I = (100000 - Range("C-1R0").Value)
ElseIf Len(Range("C-1R0")) = 6 Then
I = (1000000 - Range("C-1R0").Value)
ElseIf Len(Range("C-1R0")) = 7 Then
I = (10000000 - Range("C-1R0").Value)
End If
Range("C0R2").Value = Range("ActiveCell").Value + I
Else
Range("C0R2").Value = (Range("ActiveCell").Value -
Range("C-1R0").Value)
End If
End If
End Sub

"Peter T" wrote:

The macro recorder still does have the option to record with
relative/absolute addresses, but I doubt that's what you really want.
Difficult to say as you haven't explained what you are after
accomplishing

Regards,
Peter T


"Bob Barnes" wrote in message
...
I'll check Offset. The Macro Recorder used to (I even found how it's
supposed to still record relative addresses, but I couldn't get it to
work).
I'll try this.

Thank you - Bob

"Peter T" wrote:

To refer to a cell "relative" to a given cell use the Offset function
(see
help)
Afraid I don't follow at all the rest of what you are trying to do.

Regards,
Peter T

"Bob Barnes" wrote in message
...
The relative addresses, which I've never used, should work...because
the
Worksheet_Change would examine the Cell "triggering" the
Event...then
look
over one Column to the left, in the same row, run the code, & place
the
result in the same Column, down 2 Rows.

I would also run this on Workbook_Open, selecting the 72 Cells,
individually, by...
Range("DNine").Select --- that would be the ActiveCell..
and Calling the Subroutine to get the resulting value.

..or I could replicate the code, w/ the Names 72 times...it would
work,
and
shouldn't take long, but why do that...when a Selection of the 72
Cells,
one
at a time, then calling the Subroutine w/ relative cell adddresses
would
work...or am I missing something?

TIA - Bob

"Peter T" wrote:

What would you want ROC-1 for

Sure there's ActiveCell, but that's not necessarily the cell(s)
that
has/have just triggered a change event.

Regards,
Peter T

"Bob Barnes" wrote in message
...
Thank you.

Isn't there a R0C-1...or something like that?

I Programmed Lotus 1-2-3 years ago using their @Cell &
@Cellpointer,
but
switched to Access in 1995.

Shouldn't people be able to use "ActiveCell", or something like
that..???

"Peter T" wrote:

Start with something simple, in a test workbook on a sheet with
the
named
cell. Note you'll need to initiate by storing the value of your
DNine
cell,
in the example simply by swtiching sheets and back.

Private mOldDNine As Variant
Private Sub Worksheet_Activate()
'switch to another sheet and back to initiate
mOldDNine = Range("DNine")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vNewDNine
If Not Intersect(Target, Range("DNine")) Is Nothing Then
vNewDNine = Range("DNine").Value
MsgBox "old DNine value : " & mOldDNine & vbCr & "new
DNine
:
" &
vNewDNine
mOldDNine = vNewDNine

End If
End Sub


Post back if not clear what code after "IF" the DNine cell
changes.
Don't
post any code, just explain one or two examples with details
what
you
have
and what you want to do.

Regards,
Peter T


"Bob Barnes" wrote in
message
...
Yes...the value would change in Cell "DNine"...then compare a
"previous"
Reading in Cell "CNine" to place a calculated result in Cell
"C_D_Nine".

This compares meter readings, and if a meter "turns over" from
9999
to
0000..
then...
If Cell "DNine" = 7, and Cell "CNine" = 9997, then Cell
"C_D_Nine" =
11.
...or..
If Cell "DNine" = 7879, and Cell "CNine" = 7863, then Cell
"C_D_Nine" =
16.

TIA - Bob

"Peter T" wrote:

Just to clarify -

If Target = Range("DNine")

Do you intend that line to mean

If the Value in the cell that's just changed is the same as
the
value
in
DNine Then
or
If the cell that's just changed is the DNine cell Then

As written, the code does the former but somehow I suspect
you
mean
the
latter

Regards,
Peter T



"Bob Barnes" wrote in
message
...
I'm an Access Programmer using automation to send data to
72
different
Excel
cells.
These 72 cells all have the same relative cell
"structure"...IE,
the
Names
below are
...
"DNine" = I28
"CNine" = H28
"C_D_Nine" = I30

I'm sure I can substitute the Names below so whenever the
Worksheet_Change(ByVal Target As Range) occurs, I can have
the
ability
to calculate all 72 cell scenarios.

How...relative cell references, "Offset".. ?? TIA - Bob

sample code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("DNine") Then
If ((Range("DNine").Value - Range("CNine").Value < 0) _
And Abs(Range("DNine").Value - Range("CNine").Value)
9000)
Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = Range("DNine").Value + I
Else
Range("C_D_Nine").Value = (Range("DNine").Value -
Range("CNine").Value)
End If
End If
End Sub