Using the last few characters on a different sheet

[Solved/Closed]
Report
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
-
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
-
Hi All,

Me again - I HATE VBA! it is so simple that I get frutrated when I cant get a simple thing right.

Im trying to write a VBA script that needs to extract the last 3 characters from a cell on a different sheet in the same workbook. What I have is:

MyRequiredString = Sheets("Sheet2").Range(Right(Cells(x, 5), 3))


where x is an increasing variable which is within a for - next loop. Every time the loop happens then MyRequiredString may be different depending on where the cell (x,5) is on sheet 2. i need to use MyRequiredString in sheet 1 (which is the active sheet) [or more accurately to use these 3 characters as a condition in an "If" statement within the script for use on Sheet 1]

To put it another way, if cell (x,5) on sheet 2 is "abcdef", I want MyRequiredString to be "def"

Im sure this is a one line code, but can I work it out???

Any help would be greatly appreciated.

1 reply

This is what you need:

MyRequiredString = Right(Sheets("Sheet2").Cells(x, 5), 3)

You need to retrieve the value from the range/cell and then take the last 3 characters from it.
You were trying to put the last 3 characters of the cell as a value for the range which is not a valid value for it.
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
136
Ray - I hope you know how much of a star you are.

That was exactly what I needed. Thank you so much.

Also I appreciate the feedback on what I was doing wrong. Its only by having it pointed out that I can understand and not attempt something so silly next time.

Thanks again.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!