Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

: 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

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

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

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".

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.

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.

Related Topics:

- Len function help
- Help about RoundUp Function
- Accessing help file info for non-Excel 2007 functions from VBA
- Need Help for Macro to Perform Same Function Only on Certain Sheets
- Help with VBA function checksum
- Help in writing a function
- Need help with "inputbox" function
- help requested for using excel's built-in function in coding
- help on functions
- Help with Formatting a cell holding a custom worksheet function
- description/help with own VBA functions in Excel
- Wait function and Invisible function
- Wait function and Invisible function
- Help on a designing the help Screen
- pleass i want 2 help this Question.....so plz help me.....
- How can I call the function TotalPrice within another function?
- Excel won't calculate my function
- User Function - is optional calculation possibe?
- Automatic update of Function return value.
- String-Split Function parameter "compare"
- function as argument
- function question
- Wierd problem in VBA code re Mid function
- Problems with XL's 'MATCH' function
- Need to create function smiliar to DMAX