Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code to calculate a value in the next row
I want to use code to calculate a predetermined value depending upon the
result of an experiment I would put the result in Column A and would want the predetermined value to appear in the same row in Column B I would want to do this with multiple columns so I would not want to hard code the column name Can this be done? Private Sub Column A cell_AfterUpdate() Select Column A cell Case 5 Column B cell = 5 Case 4 Column B cell = 4.4 Case 3 Column B cell = 3.4 Case 2 Column B cell = 2 Case 1 Column B cell = 1 End Select |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code to calculate a value in the next row
How do you intend to inform the code which column to use? How do you
intend to determine what row to use? Public function GenBbasedOnA( vVal) Select Case vVal Case 5 GenBbasedOnA = 5 Case 4 GenBbasedOnA = 4.4 Case 3 GenBbasedOnA = 3.4 Case 2 GenBbasedOnA = 2 Case 1 GenBbasedOnA = 1 Case Else GenBbasedOnA = 0 End Select End Function Sub Tester1() ActiveCell.Offset(0,1).Value = GenBbasedOnA(ActiveCell.Value) End Sub -- Regards, Tom Ogilvy "Barth Wilsey" wrote in message news:DKFzb.301739$ao4.1040193@attbi_s51... I want to use code to calculate a predetermined value depending upon the result of an experiment I would put the result in Column A and would want the predetermined value to appear in the same row in Column B I would want to do this with multiple columns so I would not want to hard code the column name Can this be done? Private Sub Column A cell_AfterUpdate() Select Column A cell Case 5 Column B cell = 5 Case 4 Column B cell = 4.4 Case 3 Column B cell = 3.4 Case 2 Column B cell = 2 Case 1 Column B cell = 1 End Select |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code to calculate a value in the next row
Right click on the worksheet tab and select View Code.
In the resulting module (a sheet module), in the dropdown as the top, from the left dropdown, select Worksheet, and from the right dropdown select Change You should get a function declaration like: Private Sub Worksheet_Change(ByVal Target As Range) End Sub Target will hold a reference to the cell or cells that triggered the event. You can then test this to find out which column and then restrict your actions to specific columns or otherwise make decisions on what column contains the changed cell. Private Sub Worksheet_Change(ByVal Target As Range) if Target.Count 1 then exit sub On Error goto ErrHandler Application.EnableEvents = False Select Case Target.Column Case 1 Target.Offset(0,1).Value = GenBbasedOnA(Target.Value) Case 5 Target.Offset(0,1).Value = GenFbasedOnE(Target.Value) End Select ErrHandler: Application.EnableEvents = True End Sub I always want to go through the error handler, so there should be no Exit sub before the ErrHandler: label. The Functions (GenBbaseOnA as an example) should be in a general module. -- Regards, Tom Ogilvy "Barth Wilsey" wrote in message news:8pHzb.299926$9E1.1524620@attbi_s52... I am a newbie to coding in Excel (I have used VBA i n Word and Access). I thought that there would be a way to have Excel recognize that the value in a cell had been changed by using a function like "Private Sub_AfterUpdate()" and then code to have the value in the cell in the next column (same row) be inserted according to the rules of the function that you have provided Can you suggest a way of doing this in excel? I want to apply this to every cell in the column (each column for inserting data would have its own rules for changing the value that is being inserted) thanks in advance, Barth Wilsey "Tom Ogilvy" wrote in message ... How do you intend to inform the code which column to use? How do you intend to determine what row to use? Public function GenBbasedOnA( vVal) Select Case vVal Case 5 GenBbasedOnA = 5 Case 4 GenBbasedOnA = 4.4 Case 3 GenBbasedOnA = 3.4 Case 2 GenBbasedOnA = 2 Case 1 GenBbasedOnA = 1 Case Else GenBbasedOnA = 0 End Select End Function Sub Tester1() ActiveCell.Offset(0,1).Value = GenBbasedOnA(ActiveCell.Value) End Sub -- Regards, Tom Ogilvy "Barth Wilsey" wrote in message news:DKFzb.301739$ao4.1040193@attbi_s51... I want to use code to calculate a predetermined value depending upon the result of an experiment I would put the result in Column A and would want the predetermined value to appear in the same row in Column B I would want to do this with multiple columns so I would not want to hard code the column name Can this be done? Private Sub Column A cell_AfterUpdate() Select Column A cell Case 5 Column B cell = 5 Case 4 Column B cell = 4.4 Case 3 Column B cell = 3.4 Case 2 Column B cell = 2 Case 1 Column B cell = 1 End Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate with code | Excel Discussion (Misc queries) | |||
adding a code to calculate how much time is lost | Excel Discussion (Misc queries) | |||
VBA code to calculate the difference between rows | Excel Programming | |||
VBA code to calculate the difference between rows | Excel Programming | |||
VBA code to calculate the difference between rows | Excel Programming |