Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
I am new to this forum so bear with me! This is what I'm trying to achieve.
I did get one answer on my original question, but I did not understand the response in any way, so I have to try again. I am working on an Excel 2003 spreadsheet. The spreadsheet is already filled in with various dollar amounts (i.e. $5620) and so forth. What I need to do is multiply each one of those cell amounts by 2% so that it is ADDED to the existing amount within that cell, and so that other numbers as they are inputted will generate the additional 2% automatically. I hope this is clearer. Cheers and thank you! Linda -- Lin242 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
Linda -
In the original post, Don gave you what appears to be the best solution for your problem. I'll repost and explain it: Right click sheet tabview codeinsert thischange columns I to suit. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("I")) Is Nothing Then Exit Sub Application.EnableEvents = False If IsNumeric(Target) Then Target = Target * 1.02 Application.EnableEvents = True End Sub Sub fixincase() Application.EnableEvents = True End Sub If you right click the sheet tab (at the bottom of the spreadsheet - will say "Sheet 1", "Sheet 2", etc. and select "View Code", you will get the VBA Code for your spreadsheet. Which is likely to be empty. Copy the code he wrote, which starts with "Private Sub Worksheet" and ends with "End Sub". If you copy my cut-n-paste, remove the doohickeys. In the second line of code, where he has: "If Intersect(Target, Columns("I")) Is Nothing Then Exit Sub", change the letter "I" to whatever letter the column is that you want to multiply by 1.02 ... don't forget to leave the quotation marks. They are important. When you're done, just X out of the VBA editor and your spreadsheet should be ready to go. Hope this is clearer than mud. :) -L "Lin242" wrote: I am new to this forum so bear with me! This is what I'm trying to achieve. I did get one answer on my original question, but I did not understand the response in any way, so I have to try again. I am working on an Excel 2003 spreadsheet. The spreadsheet is already filled in with various dollar amounts (i.e. $5620) and so forth. What I need to do is multiply each one of those cell amounts by 2% so that it is ADDED to the existing amount within that cell, and so that other numbers as they are inputted will generate the additional 2% automatically. I hope this is clearer. Cheers and thank you! Linda -- Lin242 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
Lyn,
Put 1.02 in a cell and copy it. Select your range then Edit|Paste Special Select Multiply Click OK Mike "Lin242" wrote: I am new to this forum so bear with me! This is what I'm trying to achieve. I did get one answer on my original question, but I did not understand the response in any way, so I have to try again. I am working on an Excel 2003 spreadsheet. The spreadsheet is already filled in with various dollar amounts (i.e. $5620) and so forth. What I need to do is multiply each one of those cell amounts by 2% so that it is ADDED to the existing amount within that cell, and so that other numbers as they are inputted will generate the additional 2% automatically. I hope this is clearer. Cheers and thank you! Linda -- Lin242 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
Possibly the simplest solution. Except that OP specified that she wants all
future entries to do this automatically. This solution would only fix current entries. "Mike H" wrote: Lyn, Put 1.02 in a cell and copy it. Select your range then Edit|Paste Special Select Multiply Click OK Mike |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
In the original post, Don gave you what appears to be the best solution
Hmmm... I don't see Don's reply (using OE). I've noticed there have been some problems (for how long???) with the ngs and messages not apperaing. I can see some in OE but not in CDO and vice versa. -- Biff Microsoft Excel MVP "Jacinthe" wrote in message ... Linda - In the original post, Don gave you what appears to be the best solution for your problem. I'll repost and explain it: Right click sheet tabview codeinsert thischange columns I to suit. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("I")) Is Nothing Then Exit Sub Application.EnableEvents = False If IsNumeric(Target) Then Target = Target * 1.02 Application.EnableEvents = True End Sub Sub fixincase() Application.EnableEvents = True End Sub If you right click the sheet tab (at the bottom of the spreadsheet - will say "Sheet 1", "Sheet 2", etc. and select "View Code", you will get the VBA Code for your spreadsheet. Which is likely to be empty. Copy the code he wrote, which starts with "Private Sub Worksheet" and ends with "End Sub". If you copy my cut-n-paste, remove the doohickeys. In the second line of code, where he has: "If Intersect(Target, Columns("I")) Is Nothing Then Exit Sub", change the letter "I" to whatever letter the column is that you want to multiply by 1.02 ... don't forget to leave the quotation marks. They are important. When you're done, just X out of the VBA editor and your spreadsheet should be ready to go. Hope this is clearer than mud. :) -L "Lin242" wrote: I am new to this forum so bear with me! This is what I'm trying to achieve. I did get one answer on my original question, but I did not understand the response in any way, so I have to try again. I am working on an Excel 2003 spreadsheet. The spreadsheet is already filled in with various dollar amounts (i.e. $5620) and so forth. What I need to do is multiply each one of those cell amounts by 2% so that it is ADDED to the existing amount within that cell, and so that other numbers as they are inputted will generate the additional 2% automatically. I hope this is clearer. Cheers and thank you! Linda -- Lin242 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
I really appreciate your time, but even putting 1.02 in an empty box and
following the instructions you gave simply gives the actual percentage amount, it doesn't ADD it to the existing numbers which is what I really want to do. I will try your way tomorrow Jacinthe, I am not a regular Excel user, so the original instructions from Don made no sense to me. I'll see if yours are a bit clearer. It's frustrating because it appears like it should be so simple and it isn't. :) -- Lin242 "Jacinthe" wrote: Possibly the simplest solution. Except that OP specified that she wants all future entries to do this automatically. This solution would only fix current entries. "Mike H" wrote: Lyn, Put 1.02 in a cell and copy it. Select your range then Edit|Paste Special Select Multiply Click OK Mike |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
Don's reply was to the OP's first post, not this follow-up. Although I am
using (sadly) the browser-based viewer, it appears to be about 20 posts behind this one. "T. Valko" wrote: In the original post, Don gave you what appears to be the best solution Hmmm... I don't see Don's reply (using OE). I've noticed there have been some problems (for how long???) with the ngs and messages not apperaing. I can see some in OE but not in CDO and vice versa. -- Biff Microsoft Excel MVP "Jacinthe" wrote in message ... Linda - In the original post, Don gave you what appears to be the best solution for your problem. I'll repost and explain it: Right click sheet tabview codeinsert thischange columns I to suit. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("I")) Is Nothing Then Exit Sub Application.EnableEvents = False If IsNumeric(Target) Then Target = Target * 1.02 Application.EnableEvents = True End Sub Sub fixincase() Application.EnableEvents = True End Sub If you right click the sheet tab (at the bottom of the spreadsheet - will say "Sheet 1", "Sheet 2", etc. and select "View Code", you will get the VBA Code for your spreadsheet. Which is likely to be empty. Copy the code he wrote, which starts with "Private Sub Worksheet" and ends with "End Sub". If you copy my cut-n-paste, remove the doohickeys. In the second line of code, where he has: "If Intersect(Target, Columns("I")) Is Nothing Then Exit Sub", change the letter "I" to whatever letter the column is that you want to multiply by 1.02 ... don't forget to leave the quotation marks. They are important. When you're done, just X out of the VBA editor and your spreadsheet should be ready to go. Hope this is clearer than mud. :) -L "Lin242" wrote: I am new to this forum so bear with me! This is what I'm trying to achieve. I did get one answer on my original question, but I did not understand the response in any way, so I have to try again. I am working on an Excel 2003 spreadsheet. The spreadsheet is already filled in with various dollar amounts (i.e. $5620) and so forth. What I need to do is multiply each one of those cell amounts by 2% so that it is ADDED to the existing amount within that cell, and so that other numbers as they are inputted will generate the additional 2% automatically. I hope this is clearer. Cheers and thank you! Linda -- Lin242 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
Well, it's not really my way. Don wrote the code. And while I can write
code, it would have taken me a while. So credit goes to him, not me. :) The only thing I'll take credit for is the "user's manual". "Lin242" wrote: I really appreciate your time, but even putting 1.02 in an empty box and following the instructions you gave simply gives the actual percentage amount, it doesn't ADD it to the existing numbers which is what I really want to do. I will try your way tomorrow Jacinthe, I am not a regular Excel user, so the original instructions from Don made no sense to me. I'll see if yours are a bit clearer. It's frustrating because it appears like it should be so simple and it isn't. :) -- Lin242 "Jacinthe" wrote: Possibly the simplest solution. Except that OP specified that she wants all future entries to do this automatically. This solution would only fix current entries. "Mike H" wrote: Lyn, Put 1.02 in a cell and copy it. Select your range then Edit|Paste Special Select Multiply Click OK Mike |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
If you multiply a number by 1.02, that "is" adding 2% to the original
amount, as 1.02 is what you get when you add 2% to the number 1. Why don't you try Mike's suggestion, and see what numbers you get? If you are still struggling, give us some examples of: What number you started with, What operations you applied to that number, What result you got, What number you expected. -- David Biddulph "Lin242" wrote in message ... I really appreciate your time, but even putting 1.02 in an empty box and following the instructions you gave simply gives the actual percentage amount, it doesn't ADD it to the existing numbers which is what I really want to do. I will try your way tomorrow Jacinthe, I am not a regular Excel user, so the original instructions from Don made no sense to me. I'll see if yours are a bit clearer. It's frustrating because it appears like it should be so simple and it isn't. :) -- Lin242 "Jacinthe" wrote: Possibly the simplest solution. Except that OP specified that she wants all future entries to do this automatically. This solution would only fix current entries. "Mike H" wrote: Lyn, Put 1.02 in a cell and copy it. Select your range then Edit|Paste Special Select Multiply Click OK Mike |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
Yeah, I'm using Outlook Express to access these ngs.
I don't see Don's reply listed in OE or in CDO (the web based interface to the ngs) in the original thread: Constant Multiplier. I noticed this problem a few days ago. There seems to be no noticable pattern as to which messages are not showing up. -- Biff Microsoft Excel MVP "Jacinthe" wrote in message ... Don's reply was to the OP's first post, not this follow-up. Although I am using (sadly) the browser-based viewer, it appears to be about 20 posts behind this one. "T. Valko" wrote: In the original post, Don gave you what appears to be the best solution Hmmm... I don't see Don's reply (using OE). I've noticed there have been some problems (for how long???) with the ngs and messages not apperaing. I can see some in OE but not in CDO and vice versa. -- Biff Microsoft Excel MVP "Jacinthe" wrote in message ... Linda - In the original post, Don gave you what appears to be the best solution for your problem. I'll repost and explain it: Right click sheet tabview codeinsert thischange columns I to suit. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("I")) Is Nothing Then Exit Sub Application.EnableEvents = False If IsNumeric(Target) Then Target = Target * 1.02 Application.EnableEvents = True End Sub Sub fixincase() Application.EnableEvents = True End Sub If you right click the sheet tab (at the bottom of the spreadsheet - will say "Sheet 1", "Sheet 2", etc. and select "View Code", you will get the VBA Code for your spreadsheet. Which is likely to be empty. Copy the code he wrote, which starts with "Private Sub Worksheet" and ends with "End Sub". If you copy my cut-n-paste, remove the doohickeys. In the second line of code, where he has: "If Intersect(Target, Columns("I")) Is Nothing Then Exit Sub", change the letter "I" to whatever letter the column is that you want to multiply by 1.02 ... don't forget to leave the quotation marks. They are important. When you're done, just X out of the VBA editor and your spreadsheet should be ready to go. Hope this is clearer than mud. :) -L "Lin242" wrote: I am new to this forum so bear with me! This is what I'm trying to achieve. I did get one answer on my original question, but I did not understand the response in any way, so I have to try again. I am working on an Excel 2003 spreadsheet. The spreadsheet is already filled in with various dollar amounts (i.e. $5620) and so forth. What I need to do is multiply each one of those cell amounts by 2% so that it is ADDED to the existing amount within that cell, and so that other numbers as they are inputted will generate the additional 2% automatically. I hope this is clearer. Cheers and thank you! Linda -- Lin242 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
Ah, now I see Don's reply. It's in a different thread started by Lin242.
-- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Yeah, I'm using Outlook Express to access these ngs. I don't see Don's reply listed in OE or in CDO (the web based interface to the ngs) in the original thread: Constant Multiplier. I noticed this problem a few days ago. There seems to be no noticable pattern as to which messages are not showing up. -- Biff Microsoft Excel MVP "Jacinthe" wrote in message ... Don's reply was to the OP's first post, not this follow-up. Although I am using (sadly) the browser-based viewer, it appears to be about 20 posts behind this one. "T. Valko" wrote: In the original post, Don gave you what appears to be the best solution Hmmm... I don't see Don's reply (using OE). I've noticed there have been some problems (for how long???) with the ngs and messages not apperaing. I can see some in OE but not in CDO and vice versa. -- Biff Microsoft Excel MVP "Jacinthe" wrote in message ... Linda - In the original post, Don gave you what appears to be the best solution for your problem. I'll repost and explain it: Right click sheet tabview codeinsert thischange columns I to suit. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("I")) Is Nothing Then Exit Sub Application.EnableEvents = False If IsNumeric(Target) Then Target = Target * 1.02 Application.EnableEvents = True End Sub Sub fixincase() Application.EnableEvents = True End Sub If you right click the sheet tab (at the bottom of the spreadsheet - will say "Sheet 1", "Sheet 2", etc. and select "View Code", you will get the VBA Code for your spreadsheet. Which is likely to be empty. Copy the code he wrote, which starts with "Private Sub Worksheet" and ends with "End Sub". If you copy my cut-n-paste, remove the doohickeys. In the second line of code, where he has: "If Intersect(Target, Columns("I")) Is Nothing Then Exit Sub", change the letter "I" to whatever letter the column is that you want to multiply by 1.02 ... don't forget to leave the quotation marks. They are important. When you're done, just X out of the VBA editor and your spreadsheet should be ready to go. Hope this is clearer than mud. :) -L "Lin242" wrote: I am new to this forum so bear with me! This is what I'm trying to achieve. I did get one answer on my original question, but I did not understand the response in any way, so I have to try again. I am working on an Excel 2003 spreadsheet. The spreadsheet is already filled in with various dollar amounts (i.e. $5620) and so forth. What I need to do is multiply each one of those cell amounts by 2% so that it is ADDED to the existing amount within that cell, and so that other numbers as they are inputted will generate the additional 2% automatically. I hope this is clearer. Cheers and thank you! Linda -- Lin242 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
Hello David, and thanks, yes I agree, but let me give you an example then as
you suggest. My worksheet is simply cells full of dollar amounts, Columns A2 - H2, 15 rows down. For example A2 appears: $5,620, A3 is $4,645 and so on across and down. When I apply Mike's formula what I get in each cell is not the addition of 2% but the action represented addition, such as $57 for A2, what I want is that it will add it on to $5,620. Am I missing a step in the simple formula? Thanks! Jacinthe, agreed, one must give credit where credit is due :), thanks again! -- Lin242 "David Biddulph" wrote: If you multiply a number by 1.02, that "is" adding 2% to the original amount, as 1.02 is what you get when you add 2% to the number 1. Why don't you try Mike's suggestion, and see what numbers you get? If you are still struggling, give us some examples of: What number you started with, What operations you applied to that number, What result you got, What number you expected. -- David Biddulph "Lin242" wrote in message ... I really appreciate your time, but even putting 1.02 in an empty box and following the instructions you gave simply gives the actual percentage amount, it doesn't ADD it to the existing numbers which is what I really want to do. I will try your way tomorrow Jacinthe, I am not a regular Excel user, so the original instructions from Don made no sense to me. I'll see if yours are a bit clearer. It's frustrating because it appears like it should be so simple and it isn't. :) -- Lin242 "Jacinthe" wrote: Possibly the simplest solution. Except that OP specified that she wants all future entries to do this automatically. This solution would only fix current entries. "Mike H" wrote: Lyn, Put 1.02 in a cell and copy it. Select your range then Edit|Paste Special Select Multiply Click OK Mike |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying Again
The step you are missing in the simple formula is to read Mike's suggestion
and do what he said. The answers are $5,732.40 in A2 and $4,737.90 in A3. I don't understand where you are getting $57 for A2. You didn't by any chance multiply by 1.02%, rather than multiplying by 1.02, did you? -- David Biddulph "Lin242" wrote in message ... Hello David, and thanks, yes I agree, but let me give you an example then as you suggest. My worksheet is simply cells full of dollar amounts, Columns A2 - H2, 15 rows down. For example A2 appears: $5,620, A3 is $4,645 and so on across and down. When I apply Mike's formula what I get in each cell is not the addition of 2% but the action represented addition, such as $57 for A2, what I want is that it will add it on to $5,620. Am I missing a step in the simple formula? Thanks! Jacinthe, agreed, one must give credit where credit is due :), thanks again! -- Lin242 "David Biddulph" wrote: If you multiply a number by 1.02, that "is" adding 2% to the original amount, as 1.02 is what you get when you add 2% to the number 1. Why don't you try Mike's suggestion, and see what numbers you get? If you are still struggling, give us some examples of: What number you started with, What operations you applied to that number, What result you got, What number you expected. -- David Biddulph "Lin242" wrote in message ... I really appreciate your time, but even putting 1.02 in an empty box and following the instructions you gave simply gives the actual percentage amount, it doesn't ADD it to the existing numbers which is what I really want to do. I will try your way tomorrow Jacinthe, I am not a regular Excel user, so the original instructions from Don made no sense to me. I'll see if yours are a bit clearer. It's frustrating because it appears like it should be so simple and it isn't. :) -- Lin242 "Jacinthe" wrote: Possibly the simplest solution. Except that OP specified that she wants all future entries to do this automatically. This solution would only fix current entries. "Mike H" wrote: Lyn, Put 1.02 in a cell and copy it. Select your range then Edit|Paste Special Select Multiply Click OK Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|