Adding data to a cell via VBA

[Aaron1978]

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

 

[WhytheQ]

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

 

[Aaron1978]

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]

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

 

[Tom Ogilvy]

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

 

[Aaron1978]

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.

 

[Tom Ogilvy]

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

 

[Aaron1978]

Thanks Tom. You've been a great help.

Best Regards,

Aaro

 

[Aaron1978]

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

 

[Tom Ogilvy]

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

 

[Aaron1978]

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

 

[Tom Ogilvy]

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

 

[Aaron1978]

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

 

[Aaron1978]

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

 

[Tom Ogilvy]

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

 

[Aaron1978]

Brilliant. Once again - many thanks.