# Worksheet Function Right syntax help

#### DougStroud

##### Well-known Member
Need help w/ this line:
Code:
``If Cells(i, "M") = "Wheel" And Cells(i, "N").Value > 0 And Cells(Right(i, "N", 2) <> "mm") Then``

The last bit- Cells(Right(i,"N", 2) is not proper.

Trying to do this in code:
=IF(RIGHT(N33,2)<>"mm",N33 & "mm")

thanks,

ds

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### BrianB

##### Well-known Member
Bear in mind that the check is case sensitive.
Code:
``````'- partial code
If Cells(i, "M").Value = "Wheel" _
And Cells(i, "N").Value > 0 _
And Right(Cells(i, "N").Value, 2) <> "mm" Then``````

#### DougStroud

##### Well-known Member
Aahhhh-
Had that little bugger reversed, thanks.
Yes the case sensitivity is a Gotcha every time. Is there a way to circumvent this and have it search on the absolute string?

#### DougStroud

##### Well-known Member
This is the next line giving me a bit of trouble:
Code:
``If Right(Cells(i, "M").Value, 2) = "Cm" Then Right(Cells(i, "M").Value, 2) = "cm"``

It is failing on the Then portion. Any idea?

thx,

ds

#### VoG

##### Legend
Try

Code:
``If Right(Range("M" & i).Value, 2) = "Cm" Then Range("M" & i).Characters(Start:=Len(Range("M" & i).Value) - 1, Length:=1).Text = "c"``

Or using Cells notation:

Code:
``If Right(Cells(i, "M").Value, 2) = "Cm" Then Cells(i, "M").Characters(Start:=Len(Cells(i, "M").Value) - 1, Length:=1).Text = "c"``

#### DougStroud

##### Well-known Member
Thanks Vog,
I have it working. The first conditional criteria is not working quite right just yet, so I have to tweak this before I can give you a complete thumbs up.
I will post back asap.

#### DougStroud

##### Well-known Member
BrianB.'s solution worked perfectly-
I need to add the same logic to another keyword and it I am not able to get this to work out just yet.

I need the following code to evaluate two different terms, either indepently or collectively and then apply the conditions. I do not know if I can see this in the Locals window, but it appears to not like evaluating the two terms.
Column F has the term "SNOW" and Column G has the Term "BOARD" and from what I can tell after the code runs is it only evaluates the first term "SNOW" in col F.

Code:
``````If Cells(i, "F").Value = "SNOW" And Cells(i, "G").Text Like "BOARD*" And Cells(i, "M").Value > 0 _
And Right(Cells(i, "M").Value, 2) <> "Cm" Or Right(Cells(i, "M").Value, 2) <> "cm" Then
Cells(i, "M").Value = Cells(i, "M").Value & "cm"
End If
Next i``````

All this code is doing is looking for the size of snowboards, if it is a snowboard and has a size in col. M and does not have a "cm" after the size, then apply "cm" to the size.
But it is applying "cm" to all the terms w/ "SNOW".

#### VoG

##### Legend
I'm not sure what is supposed to be in column M but try

Code:
``````If Cells(i, "F").Value = "SNOW" And Cells(i, "G").Text Like "BOARD*" And Not (IsEmpty(Cells(i, "M").Value)) _
And Right(Cells(i, "M").Value, 2) <> "Cm" Or Right(Cells(i, "M").Value, 2) <> "cm" Then
Cells(i, "M").Value = Cells(i, "M").Value & "cm"``````

#### DougStroud

##### Well-known Member
I have found the first issue, the .Value is not finding the term "SNOW", when I change the object to .Text, then it begins to evaluate properly....

Still have a few more criteria to place in there. Will post back soon.

#### DougStroud

##### Well-known Member
This fails once I place the OR condition in the Then line.
It fails to place the "cm" term in the cells that meet the condition, but it places it in all cells. It is as if the second condition cancels everything out.
It does work w/ second condition of "OR" not in the sub- So the first block of code works, but I need to handle the term w/ the upper and lower values- If there is something I am missing & we can find a solution here to the existing code that would be ideal, if not, I guess the next best alternative will be to convert the upper case "C" of "Cm" to lower case prior to running this block.
Code:
``````For i = 4 To LRow
If Cells(i, "F").Text = "SNOW" And Cells(i, "G").Text Like "BOARD*" _
And Cells(i, "M").Value > 0 And Right(Cells(i, "M").Value, 2) <> "cm" Then
Cells(i, "M").Value = Cells(i, "M").Value & "cm"
End If
Next i``````

Code:
``````For i = 4 To LRow
If Cells(i, "F").Text = "SNOW" And Cells(i, "G").Text Like "BOARD*" _
And Cells(i, "M").Value > 0 And Right(Cells(i, "M").Value, 2) <> "cm" Or Right(Cells(i, "M").Text, 2) <> "Cm" Then
Cells(i, "M").Value = Cells(i, "M").Value & "cm"
End If
Next i``````

Replies
3
Views
108
Replies
0
Views
93
Replies
7
Views
107
Replies
1
Views
176
Replies
11
Views
254

1,181,647
Messages
5,931,210
Members
436,784
Latest member
amuljono

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back