Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Len function help

  Asked By: Lydia    Date: Oct 10    Category: MS Office    Views: 776
  

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

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Jarvia Miller     Answered On: Oct 10

: If Right(iGroupNum, 2) = "01" Or "02" Then

You probably need this. "02" by itself will
always test true. I didn't check for other errors.


If Right(iGroupNum, 2) = "01" Or Right(iGroupNum, 2) = "02" Then

 
Answer #2    Answered By: Allan Bailey     Answered On: Oct 10

Try simplifying your IF statement:

If Right(sHold, 2) = "01" Then
sHold = Left(sHold, Len(sHold) - 2) * 2
ElseIf Right(sHold, 2) = "02" Then
sHold = sHold * 2 + 1
Else
sHold = sHold
End If

 
Answer #3    Answered By: Baylen Smith     Answered On: Oct 10

with your help  I "simplified" to the following code. In
theory, this will allow the data to be transferred to the correct row
depending on which page I want to transfer my data to.

The code works fine when iGroupNum doesn't have a "01" or "02
appendage. But it's erroring when it gets to (Left(iGroupNum,
Len(iGroupNum) - 2))

Am I using the correct nomenclature here?

---------------------

If Right(iGroupNum, 2) = "01" Then
If TissueData Then
iGroupNum = ((Left(iGroupNum, Len(iGroupNum) - 2)) - 1) * 6 + 10
Else
iGroupNum = (Left(iGroupNum, Len(iGroupNum) - 2)) * 2 + 8
End If
ElseIf Right(iGroupNum, 2) = "02" Then
If TissueData Then
iGroupNum = ((Left(iGroupNum, Len(iGroupNum) - 2)) - 1) * 6 + 13
Else
iGroupNum = (Left(iGroupNum, Len(iGroupNum) - 2)) * 2 + 9
End If
Else
If TissueData Then
iGroupNum = ((iGroupNum - 1) * 6) + 10 'account for new group
every 3 rows
Else
iGroupNum = iGroupNum * 2 + 8 'new group each row
starting at row 5
End If
End If

 
Answer #4    Answered By: Lughaidh Fischer     Answered On: Oct 10

I assume you are either defining iGroupNum as integer or allowing it to be
defined automatically as integer.

Functions like Right, etc, work on strings, and might get in trouble with
integers.

Put an explicit definition in for iGroupNum as String (or perhaps call it
sGroupNum for correctness). Alternatively, use Mod and Int functions to act
directly on the integer values instead.

However, also ...

If Right(iGroupNum, 2) = "01" Or "02" Then

the "Or "02"" will always be true, probably. You need a complete test.

If Right(iGroupNum, 2) = "01" Or Right(iGroupNum, 2) = "02" Then

And, I don't know what you are expecting

If Right(iGroupNum, 2) <> "01" Or "02" Then
iGroupNum = iGroupNum
End If

to do. Even when you fix the If, the assignment statement doesn't do
anything. BTW for <>, you want to use "And", not "Or".

 
Answer #5    Answered By: Aalia Arain     Answered On: Oct 10

I assume you are either defining iGroupNum as integer or allowing it to
be defined automatically as integer.

Functions like Right, etc, work on strings, and might get in trouble
with integers.

Put an explicit definition in for iGroupNum as String (or perhaps call
it sGroupNum for correctness). Alternatively, use Mod and Int functions
to act directly on the integer values instead.

 
Answer #6    Answered By: Terence Mitchell     Answered On: Oct 10

It was definitely a string vs integer error like both of you noted. I
found the solution (with lots of help) with the following code:

CLng(Left(iGroupNum, Len(CStr(iGroupNum))) - 2)

 
Didn't find what you were looking for? Find more on Len function help Or get search suggestion and latest updates.




Tagged: