|
Changing consistent Cells
Below you will see a posting the I put in the Misc. group. I haven't
had any luck. As stated below I am making a program for budgeting numbers. Sometimes you want to budget by percent and sometimes by Dollars. Using 3 cells. I got a code that I used. (worksheet_change) It worked great for the 3 cells I used. Now I need to add multiple cells in the came sheet change. I will be using the same cell to be basing my calculation on, its just the dollor cell and the percent cell that are going to change locations. How do I input this multiple times on the same worksheet_change? fwday Junior Member Registered: Oct 2003 Location: Posts: 16 making a cell fixed number to a input number I am doing a caclculation. Some of the time I want to do it with a percentage and sometimes with a dollar amount. If I punch in a dollar amount I want it to come up with the persentage. If I type in the percentage I want it to come up with the dollar amount. How can I make the formula stay in a cell even if I punch something in. __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-18-2003 07:39 AM Don Guillett Guest Registered: Not Yet Location: Posts: N/A making a cell fixed number to a input number And how do you tell .20 (%) from .20 (cents) "fwday" wrote in message ... I am doing a caclculation. Some of the time I want to do it with a percentage and sometimes with a dollar amount. If I punch in a dollar amount I want it to come up with the persentage. If I type in the percentage I want it to come up with the dollar amount. How can I make the formula stay in a cell even if I punch something in. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Report this post to a moderator | IP: Logged 10-18-2003 07:55 AM fwday Junior Member Registered: Oct 2003 Location: Posts: 16 fixed Cells The Dollars and the Percents are 2 diffrent cells. Its just that sometimes I want to put in a percent and sometimes a dollar. For example. in cell A1 is a dollar amount. In the Cell A2 is the dollar amount of the pecentage of Cell B2. If I cange B2 it will change the dollar amount. Then if I go back to A2 and put in a dollar amount the formula for the equasion will be gone. I don't want it be be gone I just want it to be in the background and change the percent amount. __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-18-2003 01:02 PM RagDyer Guest Registered: Not Yet Location: Posts: N/A making a cell fixed number to a input number I think I understand what you're talking about. You have a formula in A2 that calculates a percent of B2 ? Maybe =B2*25% OR =B2*0.25 You perhaps want the results of this formula to match the dollar amount that is in A1 ? You also want to know what percent of B2 it takes to match the dollar amount in A1 ? If this is what you wish, try these ideas: Change the formula in A2 to: =B2*C1 Where YOU put a percent in C1 (decimal 0.25), and change it around until you get the dollar amount that you want, OR, Change the formula in A2 to: =B2*A1/B2 Where you automatically get the dollar amount in B2 to match the dollar amount in A1, (Don't know what this would accomplish) OR, Enter this formula anywhe =A1/B2% Where you will get the percent A1 is of B2. Of course, if I read this wrong, and you want the percent of A1 to match B2, interchange A1 and B2 in the above formulas. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "fwday" wrote in message ... The Dollars and the Percents are 2 diffrent cells. Its just that sometimes I want to put in a percent and sometimes a dollar. For example. in cell A1 is a dollar amount. In the Cell A2 is the dollar amount of the pecentage of Cell B2. If I cange B2 it will change the dollar amount. Then if I go back to A2 and put in a dollar amount the formula for the equasion will be gone. I don't want it be be gone I just want it to be in the background and change the percent amount. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Report this post to a moderator | IP: Logged 10-18-2003 02:40 PM fwday Junior Member Registered: Oct 2003 Location: Posts: 16 Attachment I am sending an attachment so that you may understand what I am looking for. I am making a worksheet to budget off of. Sometimes you budget by dollars and sometimes you budget by percent. B7 is the sales. B8 is the Wages. And C8 is the percent of wages. I want to have the option to punch in the percent of wages that I am looking for or the dollars of wages. If I make a typed change in cell B8 wages I want the C8 percent to change. If I punch in the C8 percnet I want the wages to change. This will give the flexiblity to budget eather way. Attachment: example.xls This has been downloaded 4 time(s). __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-18-2003 04:44 PM Tom Ogilvy Guest Registered: Not Yet Location: Posts: N/A making a cell fixed number to a input number Right click on the sheet tab and select view code paste in code like this Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo Errhandler If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("B8:C8")) Is Nothing Then If Len(Trim(Target.Value)) = 0 Then Exit Sub Application.EnableEvents = False If Target.Address = "$B$8" Then Debug.Print 1 If IsNumeric(Target.Offset(-1, 0)) And _ Not IsEmpty(Target.Offset(-1, 0)) Then Debug.Print 2 If Target.Offset(-1, 0).Value < 0 Then Debug.Print 3 Target.Offset(0, 1).Value = _ Target / Target.Offset(-1, 0) End If End If Else If IsNumeric(Target) Then Target.Offset(0, -1) = Target.Offset(-1, -1) * _ Target End If End If End If Errhandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy fwday wrote in message ... I am sending an attachment so that you may understand what I am looking for. I am making a worksheet to budget off of. Sometimes you budget by dollars and sometimes you budget by percent. B7 is the sales. B8 is the Wages. And C8 is the percent of wages. I want to have the option to punch in the percent of wages that I am looking for or the dollars of wages. If I make a typed change in cell B8 wages I want the C8 percent to change. If I punch in the C8 percnet I want the wages to change. This will give the flexiblity to budget eather way. File Attached: http://www.excelforum.com/attachment.php?postid=319376 (example.xls) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Report this post to a moderator | IP: Logged 10-18-2003 05:55 PM fwday Junior Member Registered: Oct 2003 Location: Posts: 16 That worked great. Now I can't get it to do it on more then one set of cells. I am doing a budgeting program And I am going to have to do this several times on one page. I keep getting a Compile error: Ambiguous name detected: Worksheet_Change __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-19-2003 03:01 PM Tom Ogilvy Guest Registered: Not Yet Location: Posts: N/A making a cell fixed number to a input number You can only have one worksheet_change macro. You would need to make the one macro work with all the cells where you want that behavior. -- Regards, Tom Ogilvy fwday wrote in message ... That worked great. Now I can't get it to do it on more then one set of cells. I am doing a budgeting program And I am going to have to do this several times on one page. I keep getting a Compile error: Ambiguous name detected: Worksheet_Change ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Report this post to a moderator | IP: Logged 10-19-2003 05:25 PM fwday Junior Member Registered: Oct 2003 Location: Posts: 16 How do I do that. Could you give me an example of more the one group of cells. I should be able then to carry it on to all the ones I need. __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-19-2003 07:24 PM fwday Junior Member Registered: Oct 2003 Location: Posts: 16 There is a copy of a sheet_change in this posting. I am looking on how to make multiple sheet_changes on one page. Please see example above and let me know. __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-21-2003 07:48 PM File Attached: http://www.excelforum.com/attachment.php?postid=325599 (example.xls) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Changing consistent Cells
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Errhandler If Target.Count 1 Then Exit Sub If Target.Column = 2 Or Target.Column = 3 Then If Len(Trim(Target.Value)) = 0 Then Exit Sub Application.EnableEvents = False If Target.Column = 2 And Target.Row Mod 2 = 0 Then If IsNumeric(Target.Offset(-1, 0)) And _ Not IsEmpty(Target.Offset(-1, 0)) Then If Target.Offset(-1, 0).Value < 0 Then Target.Offset(0, 1).Value = _ Target / Target.Offset(-1, 0) End If End If ElseIf Target.Column = 3 And Target.Row Mod 2 = 0 Then If IsNumeric(Target) Then Target.Offset(0, -1) = Target.Offset(-1, -1) * _ Target End If End If End If Errhandler: Application.EnableEvents = True End Sub This assumes your wages row is an even row and your entry will either be in column B (dollars) or in Column C (percent). The Sales row will always be an odd row. So it works on all cells in columns B and C -- Regards, Tom Ogilvy "fwday" wrote in message ... Below you will see a posting the I put in the Misc. group. I haven't had any luck. As stated below I am making a program for budgeting numbers. Sometimes you want to budget by percent and sometimes by Dollars. Using 3 cells. I got a code that I used. (worksheet_change) It worked great for the 3 cells I used. Now I need to add multiple cells in the came sheet change. I will be using the same cell to be basing my calculation on, its just the dollor cell and the percent cell that are going to change locations. How do I input this multiple times on the same worksheet_change? fwday Junior Member Registered: Oct 2003 Location: Posts: 16 making a cell fixed number to a input number I am doing a caclculation. Some of the time I want to do it with a percentage and sometimes with a dollar amount. If I punch in a dollar amount I want it to come up with the persentage. If I type in the percentage I want it to come up with the dollar amount. How can I make the formula stay in a cell even if I punch something in. __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-18-2003 07:39 AM Don Guillett Guest Registered: Not Yet Location: Posts: N/A making a cell fixed number to a input number And how do you tell .20 (%) from .20 (cents) "fwday" wrote in message ... I am doing a caclculation. Some of the time I want to do it with a percentage and sometimes with a dollar amount. If I punch in a dollar amount I want it to come up with the persentage. If I type in the percentage I want it to come up with the dollar amount. How can I make the formula stay in a cell even if I punch something in. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Report this post to a moderator | IP: Logged 10-18-2003 07:55 AM fwday Junior Member Registered: Oct 2003 Location: Posts: 16 fixed Cells The Dollars and the Percents are 2 diffrent cells. Its just that sometimes I want to put in a percent and sometimes a dollar. For example. in cell A1 is a dollar amount. In the Cell A2 is the dollar amount of the pecentage of Cell B2. If I cange B2 it will change the dollar amount. Then if I go back to A2 and put in a dollar amount the formula for the equasion will be gone. I don't want it be be gone I just want it to be in the background and change the percent amount. __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-18-2003 01:02 PM RagDyer Guest Registered: Not Yet Location: Posts: N/A making a cell fixed number to a input number I think I understand what you're talking about. You have a formula in A2 that calculates a percent of B2 ? Maybe =B2*25% OR =B2*0.25 You perhaps want the results of this formula to match the dollar amount that is in A1 ? You also want to know what percent of B2 it takes to match the dollar amount in A1 ? If this is what you wish, try these ideas: Change the formula in A2 to: =B2*C1 Where YOU put a percent in C1 (decimal 0.25), and change it around until you get the dollar amount that you want, OR, Change the formula in A2 to: =B2*A1/B2 Where you automatically get the dollar amount in B2 to match the dollar amount in A1, (Don't know what this would accomplish) OR, Enter this formula anywhe =A1/B2% Where you will get the percent A1 is of B2. Of course, if I read this wrong, and you want the percent of A1 to match B2, interchange A1 and B2 in the above formulas. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "fwday" wrote in message ... The Dollars and the Percents are 2 diffrent cells. Its just that sometimes I want to put in a percent and sometimes a dollar. For example. in cell A1 is a dollar amount. In the Cell A2 is the dollar amount of the pecentage of Cell B2. If I cange B2 it will change the dollar amount. Then if I go back to A2 and put in a dollar amount the formula for the equasion will be gone. I don't want it be be gone I just want it to be in the background and change the percent amount. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Report this post to a moderator | IP: Logged 10-18-2003 02:40 PM fwday Junior Member Registered: Oct 2003 Location: Posts: 16 Attachment I am sending an attachment so that you may understand what I am looking for. I am making a worksheet to budget off of. Sometimes you budget by dollars and sometimes you budget by percent. B7 is the sales. B8 is the Wages. And C8 is the percent of wages. I want to have the option to punch in the percent of wages that I am looking for or the dollars of wages. If I make a typed change in cell B8 wages I want the C8 percent to change. If I punch in the C8 percnet I want the wages to change. This will give the flexiblity to budget eather way. Attachment: example.xls This has been downloaded 4 time(s). __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-18-2003 04:44 PM Tom Ogilvy Guest Registered: Not Yet Location: Posts: N/A making a cell fixed number to a input number Right click on the sheet tab and select view code paste in code like this Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo Errhandler If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("B8:C8")) Is Nothing Then If Len(Trim(Target.Value)) = 0 Then Exit Sub Application.EnableEvents = False If Target.Address = "$B$8" Then Debug.Print 1 If IsNumeric(Target.Offset(-1, 0)) And _ Not IsEmpty(Target.Offset(-1, 0)) Then Debug.Print 2 If Target.Offset(-1, 0).Value < 0 Then Debug.Print 3 Target.Offset(0, 1).Value = _ Target / Target.Offset(-1, 0) End If End If Else If IsNumeric(Target) Then Target.Offset(0, -1) = Target.Offset(-1, -1) * _ Target End If End If End If Errhandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy fwday wrote in message ... I am sending an attachment so that you may understand what I am looking for. I am making a worksheet to budget off of. Sometimes you budget by dollars and sometimes you budget by percent. B7 is the sales. B8 is the Wages. And C8 is the percent of wages. I want to have the option to punch in the percent of wages that I am looking for or the dollars of wages. If I make a typed change in cell B8 wages I want the C8 percent to change. If I punch in the C8 percnet I want the wages to change. This will give the flexiblity to budget eather way. File Attached: http://www.excelforum.com/attachment.php?postid=319376 (example.xls) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Report this post to a moderator | IP: Logged 10-18-2003 05:55 PM fwday Junior Member Registered: Oct 2003 Location: Posts: 16 That worked great. Now I can't get it to do it on more then one set of cells. I am doing a budgeting program And I am going to have to do this several times on one page. I keep getting a Compile error: Ambiguous name detected: Worksheet_Change __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-19-2003 03:01 PM Tom Ogilvy Guest Registered: Not Yet Location: Posts: N/A making a cell fixed number to a input number You can only have one worksheet_change macro. You would need to make the one macro work with all the cells where you want that behavior. -- Regards, Tom Ogilvy fwday wrote in message ... That worked great. Now I can't get it to do it on more then one set of cells. I am doing a budgeting program And I am going to have to do this several times on one page. I keep getting a Compile error: Ambiguous name detected: Worksheet_Change ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Report this post to a moderator | IP: Logged 10-19-2003 05:25 PM fwday Junior Member Registered: Oct 2003 Location: Posts: 16 How do I do that. Could you give me an example of more the one group of cells. I should be able then to carry it on to all the ones I need. __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-19-2003 07:24 PM fwday Junior Member Registered: Oct 2003 Location: Posts: 16 There is a copy of a sheet_change in this posting. I am looking on how to make multiple sheet_changes on one page. Please see example above and let me know. __________________ Thank you for your help Report this post to a moderator | IP: Logged 10-21-2003 07:48 PM File Attached: http://www.excelforum.com/attachment.php?postid=325599 (example.xls) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
| All times are GMT +1. The time now is 09:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com