Worksheet Function Right syntax help

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
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
Joined
Feb 17, 2003
Messages
8,127
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
Joined
Aug 16, 2005
Messages
2,968
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
Joined
Aug 16, 2005
Messages
2,968
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
Joined
Jun 19, 2002
Messages
63,650
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
Joined
Aug 16, 2005
Messages
2,968
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
Joined
Aug 16, 2005
Messages
2,968
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
Joined
Jun 19, 2002
Messages
63,650
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
Joined
Aug 16, 2005
Messages
2,968
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
Joined
Aug 16, 2005
Messages
2,968
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top