Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for NON-presence of a string in another string | Excel Worksheet Functions | |||
Insert Leading Characters If String Is Only 7 Characters | Excel Discussion (Misc queries) | |||
Remove characters from string | Excel Programming | |||
Separate characters in a string | Excel Discussion (Misc queries) | |||
get characters from a string in excel | Excel Worksheet Functions |