Using code to calculate a value in the next row

  • Thread starter Thread starter Barth Wilsey
  • Start date Start date
B

Barth Wilsey

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