Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- Aaron1978 ------------------------------------------------------------------------ Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201 View this thread: http://www.excelforum.com/showthread...hreadid=546244 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
Thanks Tom. You've been a great help. Best Regards, Aaro -- Aaron197 ----------------------------------------------------------------------- Aaron1978's Profile: http://www.excelforum.com/member.php...fo&userid=3120 View this thread: http://www.excelforum.com/showthread.php?threadid=54624 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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 -- Aaron197 ----------------------------------------------------------------------- Aaron1978's Profile: http://www.excelforum.com/member.php...fo&userid=3120 View this thread: http://www.excelforum.com/showthread.php?threadid=54624 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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 ------------------------------------------------------------------------ Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201 View this thread: http://www.excelforum.com/showthread...hreadid=546244 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
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" wrote in message ... 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell via VBA
Brilliant. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding cell data to a record | Excel Discussion (Misc queries) | |||
Adding up within a cell during data input | Excel Discussion (Misc queries) | |||
adding data to existing data cell | Excel Worksheet Functions | |||
adding data containing name X on the cell | Excel Worksheet Functions | |||
Adding hyperlink after the existing data in the cell | Excel Programming |