|
VBA
Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here
is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
VBA
You don't really need VBA to do what you want.
Did you try this in A2 ? Format - Cells - Custom "Next Week ("0")" In A2, put this: A2: =IF(ISBLANK(A1),"",A1+1) If you want a VBA solution, come back and let us know. "NOOBY92" wrote: Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
VBA
The problem with your solution (correct me if I'm wrong) is that A1 has other
characters besides the variable (x). So A1 will never be blank. That is why I want to be able to make a character (x) a variable. "Barb Reinhardt" wrote: You don't really need VBA to do what you want. Did you try this in A2 ? Format - Cells - Custom "Next Week ("0")" In A2, put this: A2: =IF(ISBLANK(A1),"",A1+1) If you want a VBA solution, come back and let us know. "NOOBY92" wrote: Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
VBA
This will handle both
=SUBSTITUTE(A1,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},A1&"0123456789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") & MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))+1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NOOBY92" wrote in message ... The problem with your solution (correct me if I'm wrong) is that A1 has other characters besides the variable (x). So A1 will never be blank. That is why I want to be able to make a character (x) a variable. "Barb Reinhardt" wrote: You don't really need VBA to do what you want. Did you try this in A2 ? Format - Cells - Custom "Next Week ("0")" In A2, put this: A2: =IF(ISBLANK(A1),"",A1+1) If you want a VBA solution, come back and let us know. "NOOBY92" wrote: Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
VBA
Hi
Did you try Barb's elegant solution? It works just fine. The only thing that is in A1, is whatever you type in there. The remainder is merely formatting. -- Regards Roger Govier "NOOBY92" wrote in message ... The problem with your solution (correct me if I'm wrong) is that A1 has other characters besides the variable (x). So A1 will never be blank. That is why I want to be able to make a character (x) a variable. "Barb Reinhardt" wrote: You don't really need VBA to do what you want. Did you try this in A2 ? Format - Cells - Custom "Next Week ("0")" In A2, put this: A2: =IF(ISBLANK(A1),"",A1+1) If you want a VBA solution, come back and let us know. "NOOBY92" wrote: Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
VBA
Hi Bob
Maybe a slight tweak to =SUBSTITUTE(A1,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},A1&"0123456789")), SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))), MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")), SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))+1) in order to get the number inside the parens. -- Regards Roger Govier "Bob Phillips" wrote in message ... This will handle both =SUBSTITUTE(A1,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},A1&"0123456789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") & MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))+1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NOOBY92" wrote in message ... The problem with your solution (correct me if I'm wrong) is that A1 has other characters besides the variable (x). So A1 will never be blank. That is why I want to be able to make a character (x) a variable. "Barb Reinhardt" wrote: You don't really need VBA to do what you want. Did you try this in A2 ? Format - Cells - Custom "Next Week ("0")" In A2, put this: A2: =IF(ISBLANK(A1),"",A1+1) If you want a VBA solution, come back and let us know. "NOOBY92" wrote: Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
VBA
You could put a custom format on A1 as well and that would resolve that issue.
"NOOBY92" wrote: The problem with your solution (correct me if I'm wrong) is that A1 has other characters besides the variable (x). So A1 will never be blank. That is why I want to be able to make a character (x) a variable. "Barb Reinhardt" wrote: You don't really need VBA to do what you want. Did you try this in A2 ? Format - Cells - Custom "Next Week ("0")" In A2, put this: A2: =IF(ISBLANK(A1),"",A1+1) If you want a VBA solution, come back and let us know. "NOOBY92" wrote: Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
VBA
Hey Bob, thanks a lot for answering. The only problem is that what you wrote
looks like chinese to me (no, I don't speak chinese). Where do I put this? In A1 or A2. Also, I would really appreciate if you could explain what you wrote (so that next time I won't have to ask) THX "Bob Phillips" wrote: This will handle both =SUBSTITUTE(A1,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},A1&"0123456789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") & MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))+1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NOOBY92" wrote in message ... The problem with your solution (correct me if I'm wrong) is that A1 has other characters besides the variable (x). So A1 will never be blank. That is why I want to be able to make a character (x) a variable. "Barb Reinhardt" wrote: You don't really need VBA to do what you want. Did you try this in A2 ? Format - Cells - Custom "Next Week ("0")" In A2, put this: A2: =IF(ISBLANK(A1),"",A1+1) If you want a VBA solution, come back and let us know. "NOOBY92" wrote: Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
VBA
Put in A2. You cannot put it in A1, because it will overwrite the value.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NOOBY92" wrote in message ... Hey Bob, thanks a lot for answering. The only problem is that what you wrote looks like chinese to me (no, I don't speak chinese). Where do I put this? In A1 or A2. Also, I would really appreciate if you could explain what you wrote (so that next time I won't have to ask) THX "Bob Phillips" wrote: This will handle both =SUBSTITUTE(A1,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},A1&"0123456789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") & MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))+1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NOOBY92" wrote in message ... The problem with your solution (correct me if I'm wrong) is that A1 has other characters besides the variable (x). So A1 will never be blank. That is why I want to be able to make a character (x) a variable. "Barb Reinhardt" wrote: You don't really need VBA to do what you want. Did you try this in A2 ? Format - Cells - Custom "Next Week ("0")" In A2, put this: A2: =IF(ISBLANK(A1),"",A1+1) If you want a VBA solution, come back and let us know. "NOOBY92" wrote: Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
VBA
Thanks Barb,
I put the custom format in A1 but the problem is that when you change x to a number it doesn't work. You need to erase everything inside A1 and type in the number for it to work. I'm sorry if this doesn't make sense, I'll try to explain again: A1 should look like this "Week x" originally. The user should be able to change x into a number and then A1 should display "Week 2" if the user chose 2. A2 should display "Next Week (3)" "Barb Reinhardt" wrote: You could put a custom format on A1 as well and that would resolve that issue. "NOOBY92" wrote: The problem with your solution (correct me if I'm wrong) is that A1 has other characters besides the variable (x). So A1 will never be blank. That is why I want to be able to make a character (x) a variable. "Barb Reinhardt" wrote: You don't really need VBA to do what you want. Did you try this in A2 ? Format - Cells - Custom "Next Week ("0")" In A2, put this: A2: =IF(ISBLANK(A1),"",A1+1) If you want a VBA solution, come back and let us know. "NOOBY92" wrote: Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
VBA
Roger, the problem with that is that when Week x is displayed in A1 (before
the user makes changes), #value! is displayed in A2 "Roger Govier" wrote: Hi Bob Maybe a slight tweak to =SUBSTITUTE(A1,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},A1&"0123456789")), SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))), MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")), SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))+1) in order to get the number inside the parens. -- Regards Roger Govier "Bob Phillips" wrote in message ... This will handle both =SUBSTITUTE(A1,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},A1&"0123456789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") & MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))+1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NOOBY92" wrote in message ... The problem with your solution (correct me if I'm wrong) is that A1 has other characters besides the variable (x). So A1 will never be blank. That is why I want to be able to make a character (x) a variable. "Barb Reinhardt" wrote: You don't really need VBA to do what you want. Did you try this in A2 ? Format - Cells - Custom "Next Week ("0")" In A2, put this: A2: =IF(ISBLANK(A1),"",A1+1) If you want a VBA solution, come back and let us know. "NOOBY92" wrote: Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
VBA
Hi Bob assumed (as did I), that when you said Week x, x was referring to a number between 1 and 53. We could change the formula to handle x as opposed to a numeric value, but the result would be Week(0). Why not just use Week(0) as the starting value that people need to adjust. If you do, then Barb's original suggestion using cell formatting, with a simple formula, achieves your need without bothering with this longer formula. -- Regards Roger Govier "NOOBY92" wrote in message ... Roger, the problem with that is that when Week x is displayed in A1 (before the user makes changes), #value! is displayed in A2 "Roger Govier" wrote: Hi Bob Maybe a slight tweak to =SUBSTITUTE(A1,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},A1&"0123456789")), SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))), MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")), SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))+1) in order to get the number inside the parens. -- Regards Roger Govier "Bob Phillips" wrote in message ... This will handle both =SUBSTITUTE(A1,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},A1&"0123456789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") & MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))+1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NOOBY92" wrote in message ... The problem with your solution (correct me if I'm wrong) is that A1 has other characters besides the variable (x). So A1 will never be blank. That is why I want to be able to make a character (x) a variable. "Barb Reinhardt" wrote: You don't really need VBA to do what you want. Did you try this in A2 ? Format - Cells - Custom "Next Week ("0")" In A2, put this: A2: =IF(ISBLANK(A1),"",A1+1) If you want a VBA solution, come back and let us know. "NOOBY92" wrote: Hello, I am completley new to Visual Basic. I have XP SP2 and Excel 2002 Here is what I need. I want to make a regular character (for example "x") a variable. Then I want to use that variable in other cells. For Example. Cell A1- Week x /I want to make x a variable/ Cell A2- Next Week (Week ) /I want "x+1" to appear after "(Week " but I don't want anything to appear after "(Week " until the value for X is determined/ Thank You Very Much |
| All times are GMT +1. The time now is 07:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com