Relative, Offset, ActiveCell

  • Thread starter Thread starter Bob Barnes
  • Start date Start date
B

Bob Barnes

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
 
Just to clarify -
If Target = Range("DNine")
Click to expand...

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
 
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
 
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
 
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..???
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top