| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel spreadsheet
Can anyone please help me with this problem ?
Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel spreadsheet
Ron,
Before changing A1, copy B1 and pastespecial it as a value over the formula in B1. HTH, Bernie MS Excel MVP "rbane" wrote in message ... Can anyone please help me with this problem ? Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel spreadsheet
I designed a macro that works fine, thank you for your suggestion.
-- rbane "Bernie Deitrick" wrote: Ron, Before changing A1, copy B1 and pastespecial it as a value over the formula in B1. HTH, Bernie MS Excel MVP "rbane" wrote in message ... Can anyone please help me with this problem ? Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel spreadsheet
My macro is working fine, thanks to your suggestion.
-- rbane "Bernie Deitrick" wrote: Ron, Before changing A1, copy B1 and pastespecial it as a value over the formula in B1. HTH, Bernie MS Excel MVP "rbane" wrote in message ... Can anyone please help me with this problem ? Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel spreadsheet
I thought it was working fine, but I have a problem with it that I need some
help with. I'd appreciate any help anyone would be willing to give me. Thank you. -- rbane "rbane" wrote: My macro is working fine, thanks to your suggestion. -- rbane "Bernie Deitrick" wrote: Ron, Before changing A1, copy B1 and pastespecial it as a value over the formula in B1. HTH, Bernie MS Excel MVP "rbane" wrote in message ... Can anyone please help me with this problem ? Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel spreadsheet
rbane,
What's the problem? Post your code along with a description... Bernie MS Excel MVP "rbane" wrote in message ... I thought it was working fine, but I have a problem with it that I need some help with. I'd appreciate any help anyone would be willing to give me. Thank you. -- rbane "rbane" wrote: My macro is working fine, thanks to your suggestion. -- rbane "Bernie Deitrick" wrote: Ron, Before changing A1, copy B1 and pastespecial it as a value over the formula in B1. HTH, Bernie MS Excel MVP "rbane" wrote in message ... Can anyone please help me with this problem ? Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel spreadsheet
Here is my situation:
My spreadsheet has one row per day. I add an additional row every day. The values in each cell of the rows is the product of two other cells (call them 1A and 1B) that are located elsewhere on the spreadsheet. Each day I change the value of 1A and that causes the values in the cells in the rows to change accordingly. Each day before I change 1A however, I special paste all of the rows so that they do not change. I have set up a macro to do all that and that is working fine. However, I would like the macro to also automatically add another row for todays date so all I have to do is enter the value of 1A and the new row will reflect todays values. I hope that is clear and I really appreciate anyones help. -- rbane "Bernie Deitrick" wrote: rbane, What's the problem? Post your code along with a description... Bernie MS Excel MVP "rbane" wrote in message ... I thought it was working fine, but I have a problem with it that I need some help with. I'd appreciate any help anyone would be willing to give me. Thank you. -- rbane "rbane" wrote: My macro is working fine, thanks to your suggestion. -- rbane "Bernie Deitrick" wrote: Ron, Before changing A1, copy B1 and pastespecial it as a value over the formula in B1. HTH, Bernie MS Excel MVP "rbane" wrote in message ... Can anyone please help me with this problem ? Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel spreadsheet
You could use an event to do that. Copy the code below, right-click on the sheet tab, select "View
Code", and paste the code in the window that appears. You will need to set the address of 1A (the "$B$2") in the macro, as well as the column where the formulas are found: change the myCol = 3 to the number of that column. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim newVal As Variant Dim myRange As Range Dim myCol As Integer myCol = 3 If Target.Address < "$B$2" Then Exit Sub Application.EnableEvents = False newVal = Target.Value Application.Undo Set myRange = Cells(Rows.Count, myCol).End(xlUp).EntireRow myRange.Copy myRange.Insert xlDown With myRange.Offset(-1, 0).EntireRow .Copy .PasteSpecial xlPasteValues End With Target.Value = newVal Target.Select With Application .EnableEvents = True .CutCopyMode = False End With End Sub "rbane" wrote in message ... Here is my situation: My spreadsheet has one row per day. I add an additional row every day. The values in each cell of the rows is the product of two other cells (call them 1A and 1B) that are located elsewhere on the spreadsheet. Each day I change the value of 1A and that causes the values in the cells in the rows to change accordingly. Each day before I change 1A however, I special paste all of the rows so that they do not change. I have set up a macro to do all that and that is working fine. However, I would like the macro to also automatically add another row for todays date so all I have to do is enter the value of 1A and the new row will reflect todays values. I hope that is clear and I really appreciate anyones help. -- rbane "Bernie Deitrick" wrote: rbane, What's the problem? Post your code along with a description... Bernie MS Excel MVP "rbane" wrote in message ... I thought it was working fine, but I have a problem with it that I need some help with. I'd appreciate any help anyone would be willing to give me. Thank you. -- rbane "rbane" wrote: My macro is working fine, thanks to your suggestion. -- rbane "Bernie Deitrick" wrote: Ron, Before changing A1, copy B1 and pastespecial it as a value over the formula in B1. HTH, Bernie MS Excel MVP "rbane" wrote in message ... Can anyone please help me with this problem ? Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel spreadsheet
The formulas are in columns B,D,G and I.
How do I handle that? And , thank you so much. -- rbane "Bernie Deitrick" wrote: You could use an event to do that. Copy the code below, right-click on the sheet tab, select "View Code", and paste the code in the window that appears. You will need to set the address of 1A (the "$B$2") in the macro, as well as the column where the formulas are found: change the myCol = 3 to the number of that column. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim newVal As Variant Dim myRange As Range Dim myCol As Integer myCol = 3 If Target.Address < "$B$2" Then Exit Sub Application.EnableEvents = False newVal = Target.Value Application.Undo Set myRange = Cells(Rows.Count, myCol).End(xlUp).EntireRow myRange.Copy myRange.Insert xlDown With myRange.Offset(-1, 0).EntireRow .Copy .PasteSpecial xlPasteValues End With Target.Value = newVal Target.Select With Application .EnableEvents = True .CutCopyMode = False End With End Sub "rbane" wrote in message ... Here is my situation: My spreadsheet has one row per day. I add an additional row every day. The values in each cell of the rows is the product of two other cells (call them 1A and 1B) that are located elsewhere on the spreadsheet. Each day I change the value of 1A and that causes the values in the cells in the rows to change accordingly. Each day before I change 1A however, I special paste all of the rows so that they do not change. I have set up a macro to do all that and that is working fine. However, I would like the macro to also automatically add another row for todays date so all I have to do is enter the value of 1A and the new row will reflect todays values. I hope that is clear and I really appreciate anyones help. -- rbane "Bernie Deitrick" wrote: rbane, What's the problem? Post your code along with a description... Bernie MS Excel MVP "rbane" wrote in message ... I thought it was working fine, but I have a problem with it that I need some help with. I'd appreciate any help anyone would be willing to give me. Thank you. -- rbane "rbane" wrote: My macro is working fine, thanks to your suggestion. -- rbane "Bernie Deitrick" wrote: Ron, Before changing A1, copy B1 and pastespecial it as a value over the formula in B1. HTH, Bernie MS Excel MVP "rbane" wrote in message ... Can anyone please help me with this problem ? Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel spreadsheet
Bernie,
If you don't mind, I could email you a copy of my spreadsheet and that might make my problem clearer to you. Thanks in advance. -- rbane "Bernie Deitrick" wrote: You could use an event to do that. Copy the code below, right-click on the sheet tab, select "View Code", and paste the code in the window that appears. You will need to set the address of 1A (the "$B$2") in the macro, as well as the column where the formulas are found: change the myCol = 3 to the number of that column. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim newVal As Variant Dim myRange As Range Dim myCol As Integer myCol = 3 If Target.Address < "$B$2" Then Exit Sub Application.EnableEvents = False newVal = Target.Value Application.Undo Set myRange = Cells(Rows.Count, myCol).End(xlUp).EntireRow myRange.Copy myRange.Insert xlDown With myRange.Offset(-1, 0).EntireRow .Copy .PasteSpecial xlPasteValues End With Target.Value = newVal Target.Select With Application .EnableEvents = True .CutCopyMode = False End With End Sub "rbane" wrote in message ... Here is my situation: My spreadsheet has one row per day. I add an additional row every day. The values in each cell of the rows is the product of two other cells (call them 1A and 1B) that are located elsewhere on the spreadsheet. Each day I change the value of 1A and that causes the values in the cells in the rows to change accordingly. Each day before I change 1A however, I special paste all of the rows so that they do not change. I have set up a macro to do all that and that is working fine. However, I would like the macro to also automatically add another row for todays date so all I have to do is enter the value of 1A and the new row will reflect todays values. I hope that is clear and I really appreciate anyones help. -- rbane "Bernie Deitrick" wrote: rbane, What's the problem? Post your code along with a description... Bernie MS Excel MVP "rbane" wrote in message ... I thought it was working fine, but I have a problem with it that I need some help with. I'd appreciate any help anyone would be willing to give me. Thank you. -- rbane "rbane" wrote: My macro is working fine, thanks to your suggestion. -- rbane "Bernie Deitrick" wrote: Ron, Before changing A1, copy B1 and pastespecial it as a value over the formula in B1. HTH, Bernie MS Excel MVP "rbane" wrote in message ... Can anyone please help me with this problem ? Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel spreadsheet
Go ahead and mail away.... take out the spaces and change the dot to .
HTH, Bernie MS Excel MVP "rbane" wrote in message ... Bernie, If you don't mind, I could email you a copy of my spreadsheet and that might make my problem clearer to you. Thanks in advance. -- rbane "Bernie Deitrick" wrote: You could use an event to do that. Copy the code below, right-click on the sheet tab, select "View Code", and paste the code in the window that appears. You will need to set the address of 1A (the "$B$2") in the macro, as well as the column where the formulas are found: change the myCol = 3 to the number of that column. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim newVal As Variant Dim myRange As Range Dim myCol As Integer myCol = 3 If Target.Address < "$B$2" Then Exit Sub Application.EnableEvents = False newVal = Target.Value Application.Undo Set myRange = Cells(Rows.Count, myCol).End(xlUp).EntireRow myRange.Copy myRange.Insert xlDown With myRange.Offset(-1, 0).EntireRow .Copy .PasteSpecial xlPasteValues End With Target.Value = newVal Target.Select With Application .EnableEvents = True .CutCopyMode = False End With End Sub "rbane" wrote in message ... Here is my situation: My spreadsheet has one row per day. I add an additional row every day. The values in each cell of the rows is the product of two other cells (call them 1A and 1B) that are located elsewhere on the spreadsheet. Each day I change the value of 1A and that causes the values in the cells in the rows to change accordingly. Each day before I change 1A however, I special paste all of the rows so that they do not change. I have set up a macro to do all that and that is working fine. However, I would like the macro to also automatically add another row for todays date so all I have to do is enter the value of 1A and the new row will reflect todays values. I hope that is clear and I really appreciate anyones help. -- rbane "Bernie Deitrick" wrote: rbane, What's the problem? Post your code along with a description... Bernie MS Excel MVP "rbane" wrote in message ... I thought it was working fine, but I have a problem with it that I need some help with. I'd appreciate any help anyone would be willing to give me. Thank you. -- rbane "rbane" wrote: My macro is working fine, thanks to your suggestion. -- rbane "Bernie Deitrick" wrote: Ron, Before changing A1, copy B1 and pastespecial it as a value over the formula in B1. HTH, Bernie MS Excel MVP "rbane" wrote in message ... Can anyone please help me with this problem ? Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel spreadsheet
Bernie,
Thank you very much, your help was invaluable. -- rbane "Bernie Deitrick" wrote: Go ahead and mail away.... take out the spaces and change the dot to . HTH, Bernie MS Excel MVP "rbane" wrote in message ... Bernie, If you don't mind, I could email you a copy of my spreadsheet and that might make my problem clearer to you. Thanks in advance. -- rbane "Bernie Deitrick" wrote: You could use an event to do that. Copy the code below, right-click on the sheet tab, select "View Code", and paste the code in the window that appears. You will need to set the address of 1A (the "$B$2") in the macro, as well as the column where the formulas are found: change the myCol = 3 to the number of that column. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim newVal As Variant Dim myRange As Range Dim myCol As Integer myCol = 3 If Target.Address < "$B$2" Then Exit Sub Application.EnableEvents = False newVal = Target.Value Application.Undo Set myRange = Cells(Rows.Count, myCol).End(xlUp).EntireRow myRange.Copy myRange.Insert xlDown With myRange.Offset(-1, 0).EntireRow .Copy .PasteSpecial xlPasteValues End With Target.Value = newVal Target.Select With Application .EnableEvents = True .CutCopyMode = False End With End Sub "rbane" wrote in message ... Here is my situation: My spreadsheet has one row per day. I add an additional row every day. The values in each cell of the rows is the product of two other cells (call them 1A and 1B) that are located elsewhere on the spreadsheet. Each day I change the value of 1A and that causes the values in the cells in the rows to change accordingly. Each day before I change 1A however, I special paste all of the rows so that they do not change. I have set up a macro to do all that and that is working fine. However, I would like the macro to also automatically add another row for todays date so all I have to do is enter the value of 1A and the new row will reflect todays values. I hope that is clear and I really appreciate anyones help. -- rbane "Bernie Deitrick" wrote: rbane, What's the problem? Post your code along with a description... Bernie MS Excel MVP "rbane" wrote in message ... I thought it was working fine, but I have a problem with it that I need some help with. I'd appreciate any help anyone would be willing to give me. Thank you. -- rbane "rbane" wrote: My macro is working fine, thanks to your suggestion. -- rbane "Bernie Deitrick" wrote: Ron, Before changing A1, copy B1 and pastespecial it as a value over the formula in B1. HTH, Bernie MS Excel MVP "rbane" wrote in message ... Can anyone please help me with this problem ? Assume I have a row of cells named B1 and B2. Today I want the value in B1 to be the product of A1 times A2. Tomorrow, I will create another row of cells named C1 and C2. I will change the value of A1 and I want the value in C1 to be the product of the new A1 times A2. I do not want the old value in B1 to change from what it is today. Can you help me with this? Thank you very much, Ron -- rbane |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
| Importing an Excel spreadsheet from the web. | Excel Discussion (Misc queries) | |||
| Delay to startup excel spreadsheet | Excel Worksheet Functions | |||
| copy and paste excel spreadsheet into autocad | Excel Discussion (Misc queries) | |||
| Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet? | Excel Discussion (Misc queries) | |||
Linear Mode
