Hi All,

I would like to set up a piece of VBA code so that when a user adds an
entry in a cell in the speadsheet, a function is automatically run that
adds a new value to the adjacent cell. I guess the code would follow the
logic below but I can't see to work it out.

If ANUMBER is added to cell 1A Then
Run AFUNCTION(ANUMBER)
Add the value of AFUNCTION(ANUMBER) to cell 1B
End If

Any help would be greatly appreciated.

Best Regards,

Aaron


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=546244

probably loads of ways of going about this one.
try putting the below in the code window behind the actual worksheet
you are using (rather than in a normal module).
you should be able to adapt this to your needs

all the best
J

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
Range("B1") = Target.Value + 2
End If
End If
End Sub

Thanks. Your code makes the process seem clearer now. However, would you
be able to provide me with a little further advice. How would I go about
altering the code so that the value read into B1 is a function that I
have created in a module? The function will read in the value that has
been entered in A1 plus other values from else where in the sheet.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
Range("B1") = Target.Value + 2
End If
End If
End Sub

Thanks again.


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=546244

Here is my first attempt but I keep getting a runrime error '1004'. Any
ideas?


Option Explicit

Dim BackFillConstant As Single
Dim FlowStress As Single
Dim Charpy As Single
Dim FractureArea As Single
Dim Pressure As Single
Dim ArrestPressure As Single


Private Sub Worksheet_Change(ByVal Target As Range)

BackFillConstant = Range("K").Value
FlowStress = Range("FlowStress").Value
Charpy = Range("CV").Value
FractureArea = Range("A").Value
Pressure = Range("P").Value
ArrestPressure = Range("ArrestPressure").Value


If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
'Range("B1") = Target.Value + 2
Range("B1") = fnFractureVelocity(BackFillConstant,
FlowStress, Charpy, FractureArea, Pressure, ArrestPressure)
End If
End If
End Sub


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=546244

If these named ranges are on another sheet, then you need to qualify them
with the sheet name where they are located. Assumet they are on a sheet
named Data, this would work:

With Worksheets("Data")
BackFillConstant = .Range("K").Value
FlowStress = .Range("FlowStress").Value
Charpy = .Range("CV").Value
FractureArea = .Range("A").Value
Pressure = .Range("P").Value
ArrestPressure = .Range("ArrestPressure").Value
End With

--
Regards,
Tom Ogilvy


"Aaron1978" wrote
in message ...

Here is my first attempt but I keep getting a runrime error '1004'. Any
ideas?


Option Explicit

Dim BackFillConstant As Single
Dim FlowStress As Single
Dim Charpy As Single
Dim FractureArea As Single
Dim Pressure As Single
Dim ArrestPressure As Single


Private Sub Worksheet_Change(ByVal Target As Range)

BackFillConstant = Range("K").Value
FlowStress = Range("FlowStress").Value
Charpy = Range("CV").Value
FractureArea = Range("A").Value
Pressure = Range("P").Value
ArrestPressure = Range("ArrestPressure").Value


If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
'Range("B1") = Target.Value + 2
Range("B1") = fnFractureVelocity(BackFillConstant,
FlowStress, Charpy, FractureArea, Pressure, ArrestPressure)
End If
End If
End Sub


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile:
http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=546244

That's fantastic. Thanks. I now have one final favour to ask before I
can get the program to do what I set out to do;

At the moment the user enters their data into cell A1 and the output is
written to cell B1 . How would I alter the code so that the user could
paste in n rows of input data (A1 to A'n') and n rows of output data
are written to the B column (B1 to B'n'). n would vary from case to
case and the user would never know what the size of n would be. i.e.
the VBA would have to cope with n being an unknown number.

Option Explicit

Dim BackFillConstant As Single
Dim FlowStress As Single
Dim Charpy As Single
Dim FractureArea As Single
Dim Pressure As Single
Dim ArrestPressure As Single


Private Sub Worksheet_Change(ByVal Target As Range)

With Worksheets("Pipeline Data")
BackFillConstant = .Range("K").Value
FlowStress = .Range("FlowStress").Value
Charpy = .Range("CV").Value
FractureArea = .Range("A").Value
ArrestPressure = .Range("ArrestPressure").Value
End With
Pressure = Worksheets("Fracture Velocity").Range("A1").Value

If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
Range("B1") = fnFractureVelocity(BackFillConstant,
FlowStress, Charpy, FractureArea, Pressure, ArrestPressure)
End If
End If
End Sub

Once again, many thanks.


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=546244