Adding data to a cell via VBA

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

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.

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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.

Option Explicit


I don't see anywhere that you use the value entered in column A in your
formula. I assume you will need to modify your formula to do that, so I
loop throught the cell in column A and calculate for each row. I assume
your function would become

cell.Offset(0,1).Value = fnFractureVelocity( _
BackFillConstant, FlowStress, Charpy, _
FractureArea, Pressure, ArrestPressure,cell)

I also don't see any reason to declare your variables outside the change
event unless you are going to use them in some other event.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim BackFillConstant As Single
Dim FlowStress As Single
Dim Charpy As Single
Dim FractureArea As Single
Dim Pressure As Single
Dim ArrestPressure As Single
Dim cell 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(1).column = 1 Then
Application.EnableEvents = False
for each cell in Target.Columns(1).Cells
If IsNumeric(cell) Then
cell.Offset(0,1).Value = fnFractureVelocity( _
BackFillConstant, FlowStress, Charpy, _
FractureArea, Pressure, ArrestPressure)
End If
Next
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
regards,
Tom Ogilvy


"Aaron1978" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

Thanks Tom. You've been a great help.

Best Regards,

Aaron

Hi again. I've come up against a small problem. When I enter a value into any cell in the A column, the function is run and the output is written to the adjacent B column cell. However, when I delete the value in the A column I get an error and the spreadsheet no longer works. I have to close down the workbook and re-start excel. I'm assuming it is because the function fnFractureVelocity is trying to read in an empty value ffrm the A column.

Any help would be greatly appreciated.


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
Dim cell As Range

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(1).Column = 1 Then
Application.EnableEvents = False
For Each cell In Target.Columns(1).Cells
If IsNumeric(cell) Then
cell.Offset(0, 1).Value = fnFractureVelocity(BackFillConstant, FlowStress, Charpy, FractureArea, Pressure, ArrestPressure)
End If
Next
End If

ErrHandler:
Application.EnableEvents = True

End Sub

I wouldn't think so. Your function doesn't appear to use the value in
column A.

Anyway, If you think that is the problem, then you can check

If len(trim(cell)) > 0 and isnumeric(cell) then

--
Regards,
Tom Ogilvy

"Aaron1978" <[email protected]> wrote
in message news:[email protected]...
>
> Hi again. I've come up against a small problem. When I enter a value
> into any cell in the A column, the function is run and the output is
> written to the adjacent B column cell. However, when I delete the value
> in the A column I get an error and the spreadsheet no longer works. I
> have to close down the workbook and re-start excel. I'm assuming it is
> because the function fnFractureVelocity is trying to read in an empty
> value ffrm the A column.
>
> Any help would be greatly appreciated.
>
>
> 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
> Dim cell As Range
>
> 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(1).Column = 1 Then
> Application.EnableEvents = False
> For Each cell In Target.Columns(1).Cells
> If IsNumeric(cell) Then
> cell.Offset(0, 1).Value =
> fnFractureVelocity(BackFillConstant, FlowStress, Charpy, FractureArea,
> Pressure, ArrestPressure)
> End If
> Next
> End If
>
> ErrHandler:
> Application.EnableEvents = True
>
> 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
>

Thanks again. Actually, that was another problem I was going to ask you about. The value that is read from column A is

Pressure = Worksheets("Fracture Velocity").Range("A1").Value

The problem here is that for each succsessive value that is entered in column A after A1, the function is always reading in the value from A1. I would like the function to read in each adjacent A value. i.e. A1 to be used in the function written to B1, A2 to be used in the function written to B2, etc....

Best Regards,

Aaron

cell refers to the value you need for pressure. Since I have no idea of the
name of the sheet where the event is occuring it wasn't obvious to me that
pressure was to be the cell in column A.

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
Dim cell As Range

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

If Target(1).Column = 1 Then
Application.EnableEvents = False
For Each cell In Target.Columns(1).Cells
If IsNumeric(cell) and len(trim(cell)) <> 0 Then
'Pressure = Worksheets("Fracture Velocity").Range("A1").Value
Pressure = cell
cell.Offset(0, 1).Value = _
fnFractureVelocity(BackFillConstant, _
FlowStress, Charpy, FractureArea, _
Pressure, ArrestPressure)
End If
Next
End If

ErrHandler:
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy


"Aaron1978" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks again. Actually, that was another problem I was going to ask you
> about. The value that is read from column A is
>
> Pressure = Worksheets("Fracture Velocity").Range("A1").Value
>
> The problem here is that for each succsessive value that is entered in
> column A after A1, the function is always reading in the value from A1.
> I would like the function to read in each adjacent A value. i.e. A1 to
> be used in the function written to B1, A2 to be used in the function
> written to B2, etc....
>
> 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
>

Thanks dude. You've just saved me hours of my time if I had to work all that out on my own. I was kind of there but I needed a shove in the right direction. Good work fella!

Best Regards,

Aaron

Hi, me again. The lines of code you have advised to use work great. However, I'm not 100% sure what each line is doing which is giving me difficulties in modifying it. At the moment the user enters the data in column A and the value of the function is written to column B. I would like to shift this across so the user enters the data in column B and the function output appears in column C. But like I said, I'm not fully understanding the code.

If you have the time I would appreciate it if you could give alittle explanation of the below code. I get the jist of what is happening but I'm not 100%; specifically the parts:

'Target(1).Column = 1'
'Application.EnableEvents = False'
'Target.Columns(1).Cells'
'Len(Trim(cell)) <> 0'


If Target(1).Column = 1 Then
Application.EnableEvents = False
For Each cell In Target.Columns(1).Cells
If IsNumeric(cell) And Len(Trim(cell)) <> 0 Then
Pressure = cell
cell.Offset(0, 1).Value = fnFractureVelocity(BackFillConstant, FlowStress, Charpy, FractureArea, Pressure, ArrestPressure)
End If
Next
End If

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
Dim cell As Range

Private Sub Worksheet_Change(ByVal Target As Range)

' target holds a reference to the cell(s) that triggered the event
' you say it can be multiple cells.

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

'Determine which column it the trigger column
' only act if that column is column 2
' If a multicell range, determine by the upper
' left corner of the range "Target(1)"

If Target(1).Column = 2 Then
' turn off events so if you make a change in a cell
' by using the code, it won't trigger the change event
' again.
Application.EnableEvents = False
' in case you did something to B3:F6 for example, only
' work with column 2, us Target.Columns(1).Cells
' loop through all cells in column 2 (could be only 1)
For Each cell In Target.Columns(1).Cells
' you said you need to exclude empty cells in column B
If IsNumeric(cell) and len(trim(cell)) <> 0 Then
'Pressure = Worksheets("Fracture Velocity").Range("A1").Value
Pressure = cell
' put the result in the cell to the right of cell - in this case
' in column C of the same row as Cell
cell.Offset(0, 1).Value = _
fnFractureVelocity(BackFillConstant, _
FlowStress, Charpy, FractureArea, _
Pressure, ArrestPressure)
End If
Next
End If

ErrHandler:
' turn events back on
Application.EnableEvents = True

End Sub


--
Regards,
Tom Ogilvy

"Aaron1978" <[email protected]> wrote
in message news:[email protected]...
>
> Hi, me again. The lines of code you have advised to use work great.
> However, I'm not 100% sure what each line is doing which is giving me
> difficulties in modifying it. At the moment the user enters the data in
> column A and the value of the function is written to column B. I would
> like to shift this across so the user enters the data in column B and
> the function output appears in column C. But like I said, I'm not fully
> understanding the code.
>
> If you have the time I would appreciate it if you could give alittle
> explanation of the below code. I get the jist of what is happening but
> I'm not 100%; specifically the parts:
>
> 'Target(1).Column = 1'
> 'Application.EnableEvents = False'
> 'Target.Columns(1).Cells'
> 'Len(Trim(cell)) <> 0'
>
>
> If Target(1).Column = 1 Then
> Application.EnableEvents = False
> For Each cell In Target.Columns(1).Cells
> If IsNumeric(cell) And Len(Trim(cell)) <> 0 Then
> Pressure = cell
> cell.Offset(0, 1).Value = fnFractureVelocity(BackFillConstant,
> FlowStress, Charpy, FractureArea, Pressure, ArrestPressure)
> End If
> Next
> End If
>
>
> --
> Aaron1978
> ------------------------------------------------------------------------
> Aaron1978's Profile:
http://www.excelforum.com/member.php...o&userid=31201
> View this thread: http://www.excelforum.com/showthread...hreadid=546244
>

Brilliant. Once again - many thanks.