| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seeking some VB code help.
I'm trying to do the following:
iGroupNum = variable IF (the right 2 digits of iGroupNum) = "01" or "02" then If (the right 2 of iGroupNum) = "01" then iGroupNum = whatever is left minus the 2 right digits (i.e. 3001 would be igroupnum= 30) * 2 Else If (the right 2 digits of iGroupNum) = "02" then iGroupNumb= whatever is left minus the 2 right digits * 2 + 1 End If Else (the right 2 digits of iGroupNum < "01" or "02" then iGroupNum = iGroupNum* 2 End If Essentially, I'm trying to move cells from one page with a designated group number to another page. They will go into every other row except for when the right 2 digits of the designated group number is "01" or "02". In this case they will end up in consecutive rows. So far I have: If Right(iGroupNum, 2) = "01" Or "02" Then If Right(iGroupNum, 2) = "01" Then iGroupNum = Left(iGroupNum, Len(iGroupNum) - 2) * 2 Else iGroupNum = iGroupNum * 2 + 1 End If End If If Right(iGroupNum, 2) < "01" Or "02" Then iGroupNum = iGroupNum End If Unfortunately the code is going into the first IF statement even if iGroupNum = 2 (should only go in if it were 201 or 202) End If Help? Newbie VB gal. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Seeking some VB code help.
JuniperTree wrote:
I'm trying to do the following: iGroupNum = variable IF (the right 2 digits of iGroupNum) = "01" or "02" then If (the right 2 of iGroupNum) = "01" then iGroupNum = whatever is left minus the 2 right digits (i.e. 3001 would be igroupnum= 30) * 2 Else If (the right 2 digits of iGroupNum) = "02" then iGroupNumb= whatever is left minus the 2 right digits * 2 + 1 End If Else (the right 2 digits of iGroupNum < "01" or "02" then iGroupNum = iGroupNum* 2 End If Essentially, I'm trying to move cells from one page with a designated group number to another page. They will go into every other row except for when the right 2 digits of the designated group number is "01" or "02". In this case they will end up in consecutive rows. So far I have: If Right(iGroupNum, 2) = "01" Or "02" Then If Right(iGroupNum, 2) = "01" Then iGroupNum = Left(iGroupNum, Len(iGroupNum) - 2) * 2 Else iGroupNum = iGroupNum * 2 + 1 End If End If If Right(iGroupNum, 2) < "01" Or "02" Then iGroupNum = iGroupNum End If Unfortunately the code is going into the first IF statement even if iGroupNum = 2 (should only go in if it were 201 or 202) End If Help? Newbie VB gal. I am not really sure of the significance of the code If Right(iGroupNum, 2) < "01" Or "02" Then iGroupNum = iGroupNum End If Here is your code with a slight change (you can't just use OR between two conditions for an IF statement, you need to have If x=y or if x=z then Right now, the code checks to see if the right two characters are "01", or "02". If they are, then it takes the left two characters and multiplies them by 2. If the right two characters are not "01" or "02" then igroupnum = igroupnum*2+1 Is that what you were trying to do? Why is the other IF statement making igroupnum=igroupnum? If Right(igroupnum, 2) = "01" Or Right(igroupnum, 2) = "02" Then If Right(igroupnum, 2) = "01" Then igroupnum = Left(igroupnum, Len(igroupnum) - 2) * 2 Else igroupnum = igroupnum * 2 + 1 End If End If I am thinking that you want the code to do something like the following: If Right(igroupnum, 2) = "01" Or Right(igroupnum, 2) = "02" Then If Right(igroupnum, 2) = "01" Then igroupnum = Left(igroupnum, Len(igroupnum) - 2) * 2 ElseIf Right(igroupnum, 2) = "02" Then igroupnum = igroupnum * 2 + 1 Else igroupnum = igroupnum End If End If Please let me know if that helps. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Seeking some VB code help.
Thanks so much for the feedback. I actually changed my code around (I
can send it to you if you're interested) but I'm now having problems with the statement: =LEFT(iGroupNum,LEN(iGroupNum)-2)) When I go into my spreadsheet and substitute iGroupNum for the cell that contains the number (ie 301) Excel just kind of hangs. Any suggestions? Is it because it's a integer and not a string? meatshield wrote: JuniperTree wrote: I'm trying to do the following: iGroupNum = variable IF (the right 2 digits of iGroupNum) = "01" or "02" then If (the right 2 of iGroupNum) = "01" then iGroupNum = whatever is left minus the 2 right digits (i.e. 3001 would be igroupnum= 30) * 2 Else If (the right 2 digits of iGroupNum) = "02" then iGroupNumb= whatever is left minus the 2 right digits * 2 + 1 End If Else (the right 2 digits of iGroupNum < "01" or "02" then iGroupNum = iGroupNum* 2 End If Essentially, I'm trying to move cells from one page with a designated group number to another page. They will go into every other row except for when the right 2 digits of the designated group number is "01" or "02". In this case they will end up in consecutive rows. So far I have: If Right(iGroupNum, 2) = "01" Or "02" Then If Right(iGroupNum, 2) = "01" Then iGroupNum = Left(iGroupNum, Len(iGroupNum) - 2) * 2 Else iGroupNum = iGroupNum * 2 + 1 End If End If If Right(iGroupNum, 2) < "01" Or "02" Then iGroupNum = iGroupNum End If Unfortunately the code is going into the first IF statement even if iGroupNum = 2 (should only go in if it were 201 or 202) End If Help? Newbie VB gal. I am not really sure of the significance of the code If Right(iGroupNum, 2) < "01" Or "02" Then iGroupNum = iGroupNum End If Here is your code with a slight change (you can't just use OR between two conditions for an IF statement, you need to have If x=y or if x=z then Right now, the code checks to see if the right two characters are "01", or "02". If they are, then it takes the left two characters and multiplies them by 2. If the right two characters are not "01" or "02" then igroupnum = igroupnum*2+1 Is that what you were trying to do? Why is the other IF statement making igroupnum=igroupnum? If Right(igroupnum, 2) = "01" Or Right(igroupnum, 2) = "02" Then If Right(igroupnum, 2) = "01" Then igroupnum = Left(igroupnum, Len(igroupnum) - 2) * 2 Else igroupnum = igroupnum * 2 + 1 End If End If I am thinking that you want the code to do something like the following: If Right(igroupnum, 2) = "01" Or Right(igroupnum, 2) = "02" Then If Right(igroupnum, 2) = "01" Then igroupnum = Left(igroupnum, Len(igroupnum) - 2) * 2 ElseIf Right(igroupnum, 2) = "02" Then igroupnum = igroupnum * 2 + 1 Else igroupnum = igroupnum End If End If Please let me know if that helps. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Seeking some VB code help.
Yes, I think you are correct (about an integer vs string).
Specifically, the problem is with the LEN function, although it usually isn't good practice to compare strings and numbers. When LEN is used with a non-string, it returns the amount of memory used to store the variable. So, for an integer, it will return 2 regardless for any number between -32,768 and 32,767. I am not sure why excel hangs for you, but for me, it just returns an incorrect result. I've made some changes to the code to convert between strings and long (you can use integer if all of your numbers are going to be rather small), and I THINK it will work correctly now. If you are still having problems, please let me know, and post some of your sample code so I can try and see what I going on. I hope this helps Sub LEN_integer Dim igroupnum As Long igroupnum = ActiveCell.Value 'this assumes that the activecell is a long integer, if it isn't, this will return an error '=Clng(LEFT(iGroupNum,LEN(cstr(iGroupNum))-2))) If Right(igroupnum, 2) = "01" Or Right(igroupnum, 2) = "02" Then If Right(igroupnum, 2) = "01" Then igroupnum = CLng(Left(igroupnum, Len(CStr(igroupnum)) - 2) * 2) ElseIf Right(igroupnum, 2) = "02" Then igroupnum = igroupnum * 2 + 1 Else igroupnum = igroupnum End If End If End Sub On Jan 23, 5:32 pm, "JuniperTree" wrote: Thanks so much for the feedback. I actually changed my code around (I can send it to you if you're interested) but I'm now having problems with the statement: =LEFT(iGroupNum,LEN(iGroupNum)-2)) When I go into my spreadsheet and substitute iGroupNum for the cell that contains the number (ie 301) Excel just kind of hangs. Any suggestions? Is it because it's a integer and not a string? meatshield wrote: JuniperTree wrote: I'm trying to do the following: iGroupNum = variable IF (the right 2 digits of iGroupNum) = "01" or "02" then If (the right 2 of iGroupNum) = "01" then iGroupNum = whatever is left minus the 2 right digits (i.e. 3001 would be igroupnum= 30) * 2 Else If (the right 2 digits of iGroupNum) = "02" then iGroupNumb= whatever is left minus the 2 right digits * 2 + 1 End If Else (the right 2 digits of iGroupNum < "01" or "02" then iGroupNum = iGroupNum* 2 End If Essentially, I'm trying to move cells from one page with a designated group number to another page. They will go into every other row except for when the right 2 digits of the designated group number is "01" or "02". In this case they will end up in consecutive rows. So far I have: If Right(iGroupNum, 2) = "01" Or "02" Then If Right(iGroupNum, 2) = "01" Then iGroupNum = Left(iGroupNum, Len(iGroupNum) - 2) * 2 Else iGroupNum = iGroupNum * 2 + 1 End If End If If Right(iGroupNum, 2) < "01" Or "02" Then iGroupNum = iGroupNum End If Unfortunately the code is going into the first IF statement even if iGroupNum = 2 (should only go in if it were 201 or 202) End If Help? Newbie VB gal. I am not really sure of the significance of the code If Right(iGroupNum, 2) < "01" Or "02" Then iGroupNum = iGroupNum End If Here is your code with a slight change (you can't just use OR between two conditions for an IF statement, you need to have If x=y or if x=z then Right now, the code checks to see if the right two characters are "01", or "02". If they are, then it takes the left two characters and multiplies them by 2. If the right two characters are not "01" or "02" then igroupnum = igroupnum*2+1 Is that what you were trying to do? Why is the other IF statement making igroupnum=igroupnum? If Right(igroupnum, 2) = "01" Or Right(igroupnum, 2) = "02" Then If Right(igroupnum, 2) = "01" Then igroupnum = Left(igroupnum, Len(igroupnum) - 2) * 2 Else igroupnum = igroupnum * 2 + 1 End If End If I am thinking that you want the code to do something like the following: If Right(igroupnum, 2) = "01" Or Right(igroupnum, 2) = "02" Then If Right(igroupnum, 2) = "01" Then igroupnum = Left(igroupnum, Len(igroupnum) - 2) * 2 ElseIf Right(igroupnum, 2) = "02" Then igroupnum = igroupnum * 2 + 1 Else igroupnum = igroupnum End If End If Please let me know if that helps. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Seeking some VB code help.
CLng(Left(igroupnum, Len(CStr(igroupnum)) worked! Thanks millions for
your help! :) On Jan 24, 5:54 am, "meatshield" wrote: Yes, I think you are correct (about an integer vs string). Specifically, the problem is with the LEN function, although it usually isn't good practice to compare strings and numbers. When LEN is used with a non-string, it returns the amount of memory used to store the variable. So, for an integer, it will return 2 regardless for any number between -32,768 and 32,767. I am not sure why excel hangs for you, but for me, it just returns an incorrect result. I've made some changes to the code to convert between strings and long (you can use integer if all of your numbers are going to be rather small), and I THINK it will work correctly now. If you are still having problems, please let me know, and post some of your sample code so I can try and see what I going on. I hope this helps Sub LEN_integer Dim igroupnum As Long igroupnum = ActiveCell.Value 'this assumes that the activecell is a long integer, if it isn't, this will return an error '=Clng(LEFT(iGroupNum,LEN(cstr(iGroupNum))-2))) If Right(igroupnum, 2) = "01" Or Right(igroupnum, 2) = "02" Then If Right(igroupnum, 2) = "01" Then igroupnum = CLng(Left(igroupnum, Len(CStr(igroupnum)) - 2) * 2) ElseIf Right(igroupnum, 2) = "02" Then igroupnum = igroupnum * 2 + 1 Else igroupnum = igroupnum End If End If End Sub On Jan 23, 5:32 pm, "JuniperTree" wrote: Thanks so much for the feedback. I actually changed my code around (I can send it to you if you're interested) but I'm now having problems with the statement: =LEFT(iGroupNum,LEN(iGroupNum)-2)) When I go into my spreadsheet and substitute iGroupNum for the cell that contains the number (ie 301) Excel just kind of hangs. Any suggestions? Is it because it's a integer and not a string? meatshield wrote: JuniperTree wrote: I'm trying to do the following: iGroupNum = variable IF (the right 2 digits of iGroupNum) = "01" or "02" then If (the right 2 of iGroupNum) = "01" then iGroupNum = whatever is left minus the 2 right digits (i.e. 3001 would be igroupnum= 30) * 2 Else If (the right 2 digits of iGroupNum) = "02" then iGroupNumb= whatever is left minus the 2 right digits * 2 + 1 End If Else (the right 2 digits of iGroupNum < "01" or "02" then iGroupNum = iGroupNum* 2 End If Essentially, I'm trying to move cells from one page with a designated group number to another page. They will go into every other row except for when the right 2 digits of the designated group number is "01" or "02". In this case they will end up in consecutive rows. So far I have: If Right(iGroupNum, 2) = "01" Or "02" Then If Right(iGroupNum, 2) = "01" Then iGroupNum = Left(iGroupNum, Len(iGroupNum) - 2) * 2 Else iGroupNum = iGroupNum * 2 + 1 End If End If If Right(iGroupNum, 2) < "01" Or "02" Then iGroupNum = iGroupNum End If Unfortunately the code is going into the first IF statement even if iGroupNum = 2 (should only go in if it were 201 or 202) End If Help? Newbie VB gal. I am not really sure of the significance of the code If Right(iGroupNum, 2) < "01" Or "02" Then iGroupNum = iGroupNum End If Here is your code with a slight change (you can't just use OR between two conditions for an IF statement, you need to have If x=y or if x=z then Right now, the code checks to see if the right two characters are "01", or "02". If they are, then it takes the left two characters and multiplies them by 2. If the right two characters are not "01" or "02" then igroupnum = igroupnum*2+1 Is that what you were trying to do? Why is the other IF statement making igroupnum=igroupnum? If Right(igroupnum, 2) = "01" Or Right(igroupnum, 2) = "02" Then If Right(igroupnum, 2) = "01" Then igroupnum = Left(igroupnum, Len(igroupnum) - 2) * 2 Else igroupnum = igroupnum * 2 + 1 End If End If I am thinking that you want the code to do something like the following: If Right(igroupnum, 2) = "01" Or Right(igroupnum, 2) = "02" Then If Right(igroupnum, 2) = "01" Then igroupnum = Left(igroupnum, Len(igroupnum) - 2) * 2 ElseIf Right(igroupnum, 2) = "02" Then igroupnum = igroupnum * 2 + 1 Else igroupnum = igroupnum End If End If Please let me know if that helps. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| seeking help | Excel Worksheet Functions | |||
| Seeking the character code for entering a Hard Return in a cell? | Excel Discussion (Misc queries) | |||
| Seeking Help with a forumal. | Excel Programming | |||
| Seeking help from a GURU | Excel Worksheet Functions | |||
| Seeking example code | Excel Programming | |||
Linear Mode
