| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Offset and ActiveCell | Excel Programming | |||
| Reference relative to activecell | Excel Programming | |||
| If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
| Activecell Offset | Excel Programming | |||
| ActiveCell.Offset w/ VBA | Excel Programming | |||
Hybrid Mode
