Check last two characters of a string.
 

Hi I have a macro where I am trying to copy the entire row of data to a new
sheet if the last two characters of a cell meets one of two conditions.

What I have so far is...

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

Sub MoveRowsToBLM10()

Dim srcRange As Range
Dim dstRange As Range
Const srcSheetName = "INTERLINE_COUPON LISTING_JA (2)"
Const dstSheetName = "sheet100"
Dim Roffset As Long
Dim dstRow As Long

Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "sheet100"

Worksheets(srcSheetName).Select
Range("B1").Select

Do Until ActiveCell.Row = 65535

If ActiveCell.Offset(Roffset, 0) = "78" Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
If ActiveCell.Offset(Roffset, 0) = "88" Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
Roffset = Roffset + 1
Loop

Set srcRange = Nothing
Set dstRange = Nothing
End Sub

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

But I need to change this somehow so instead reading

If ActiveCell.Offset(Roffset, 0) = "78"

And

If ActiveCell.Offset(Roffset, 0) = "88"

I need it to look to see if just the last two characters of the cell are
"78" or "88"

Can anyone point give me some tips on doing this please.

Regards
John

Check last two characters of a string.
 

You nee to use right

If right(ActiveCell.Offset(Roffset, 0),2) = "78" Then


"mg_sv_r" wrote:

Hi I have a macro where I am trying to copy the entire row of data to a new
sheet if the last two characters of a cell meets one of two conditions.

What I have so far is...

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

Sub MoveRowsToBLM10()

Dim srcRange As Range
Dim dstRange As Range
Const srcSheetName = "INTERLINE_COUPON LISTING_JA (2)"
Const dstSheetName = "sheet100"
Dim Roffset As Long
Dim dstRow As Long

Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "sheet100"

Worksheets(srcSheetName).Select
Range("B1").Select

Do Until ActiveCell.Row = 65535

If ActiveCell.Offset(Roffset, 0) = "78" Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
If ActiveCell.Offset(Roffset, 0) = "88" Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
Roffset = Roffset + 1
Loop

Set srcRange = Nothing
Set dstRange = Nothing
End Sub

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

But I need to change this somehow so instead reading

If ActiveCell.Offset(Roffset, 0) = "78"

And

If ActiveCell.Offset(Roffset, 0) = "88"

I need it to look to see if just the last two characters of the cell are
"78" or "88"

Can anyone point give me some tips on doing this please.

Regards
John

Check last two characters of a string.
 

Thank you Joel!

A lot simpler than I thought!

Very much appreciated.

John

"Joel" wrote:

You nee to use right

If right(ActiveCell.Offset(Roffset, 0),2) = "78" Then


"mg_sv_r" wrote:

Hi I have a macro where I am trying to copy the entire row of data to a new
sheet if the last two characters of a cell meets one of two conditions.

What I have so far is...

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

Sub MoveRowsToBLM10()

Dim srcRange As Range
Dim dstRange As Range
Const srcSheetName = "INTERLINE_COUPON LISTING_JA (2)"
Const dstSheetName = "sheet100"
Dim Roffset As Long
Dim dstRow As Long

Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "sheet100"

Worksheets(srcSheetName).Select
Range("B1").Select

Do Until ActiveCell.Row = 65535

If ActiveCell.Offset(Roffset, 0) = "78" Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
If ActiveCell.Offset(Roffset, 0) = "88" Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
Roffset = Roffset + 1
Loop

Set srcRange = Nothing
Set dstRange = Nothing
End Sub

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

But I need to change this somehow so instead reading

If ActiveCell.Offset(Roffset, 0) = "78"

And

If ActiveCell.Offset(Roffset, 0) = "88"

I need it to look to see if just the last two characters of the cell are
"78" or "88"

Can anyone point give me some tips on doing this please.

Regards
John

Check last two characters of a string.
 

Unless I am misreading your code, it looks like you are executing the
**identical** code when the last two numbers are either 78 or 88. If I have
read your code correctly, you do not need two separate If-Then blocks (one
for each matching last two values), you can use a single If-Then block with
either of these If-Then statements as the testing condition...

If your right two numbers are as shown (78 and 88)
================================================== =
If Right(ActiveCell.Offset(Roffset, 0),2) Like "[78]8" Then

If your number were representative (not necessarily sharing digits)
================================================== =
If Right(ActiveCell.Offset(Roffset, 0),2) = "78" Or _
Right(ActiveCell.Offset(Roffset, 0),2) = "88" Then

Rick


"mg_sv_r" wrote in message
...
Thank you Joel!

A lot simpler than I thought!

Very much appreciated.

John

"Joel" wrote:

You nee to use right

If right(ActiveCell.Offset(Roffset, 0),2) = "78" Then


"mg_sv_r" wrote:

Hi I have a macro where I am trying to copy the entire row of data to a
new
sheet if the last two characters of a cell meets one of two conditions.

What I have so far is...

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

Sub MoveRowsToBLM10()

Dim srcRange As Range
Dim dstRange As Range
Const srcSheetName = "INTERLINE_COUPON LISTING_JA (2)"
Const dstSheetName = "sheet100"
Dim Roffset As Long
Dim dstRow As Long

Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "sheet100"

Worksheets(srcSheetName).Select
Range("B1").Select

Do Until ActiveCell.Row = 65535

If ActiveCell.Offset(Roffset, 0) = "78" Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
If ActiveCell.Offset(Roffset, 0) = "88" Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
Roffset = Roffset + 1
Loop

Set srcRange = Nothing
Set dstRange = Nothing
End Sub

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

But I need to change this somehow so instead reading

If ActiveCell.Offset(Roffset, 0) = "78"

And

If ActiveCell.Offset(Roffset, 0) = "88"

I need it to look to see if just the last two characters of the cell
are
"78" or "88"

Can anyone point give me some tips on doing this please.

Regards
John