Ignoring Certain Characters In a Cell Using VBA
 

I have a problem with some code I have. It is setup right now so that
data entered into column A must match or it is then moved to column B.
The data in column A is always going to be 13 characters long

The problem is the last 5 digits of the data are always going to be
different and the first 8 are always going to be the same. So basically
the code needs to be setup to recognize the first 8 digits of data and
ignore the last 5.

Is there any simple way to do that?


--
Lil Pun
------------------------------------------------------------------------
Lil Pun's Profile: http://www.excelforum.com/member.php...o&userid=33840
View this thread: http://www.excelforum.com/showthread...hreadid=549758

Ignoring Certain Characters In a Cell Using VBA
 

Use the Left function to extract the left-most 8 digits.

Res = Left(Range("A1").Value,8)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Lil Pun"
wrote in message
...

I have a problem with some code I have. It is setup right now
so that
data entered into column A must match or it is then moved to
column B.
The data in column A is always going to be 13 characters long

The problem is the last 5 digits of the data are always going
to be
different and the first 8 are always going to be the same. So
basically
the code needs to be setup to recognize the first 8 digits of
data and
ignore the last 5.

Is there any simple way to do that?


--
Lil Pun
------------------------------------------------------------------------
Lil Pun's Profile:
http://www.excelforum.com/member.php...o&userid=33840
View this thread:
http://www.excelforum.com/showthread...hreadid=549758

Ignoring Certain Characters In a Cell Using VBA
 

Thanks. Will this work for the entire column or just cell A1 though?
need it to work for the entire column.

Here is how I have the sort setup now:


Private Sub Worksheet_Activate()
avoidloop = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errorhandler
If avoidloop And Trim(Target) < "" Then
If Target = "1" Then
Range("C2").Select
Application.SendKeys "{F2}"
Else
Select Case (ActiveCell.Column)
Case 1
avoidloop = False
If ActiveSheet.Rows(2).Columns(1).Value
Target Then
ActiveSheet.Rows(ActiveCell.Row
1).Columns(1).Value = Target
ActiveSheet.Rows(ActiveCell.Row
1).Columns(2).Value = ""
avoidloop = True
Else
ActiveSheet.Rows(ActiveCell.Row
1).Columns(2).Value = Target
ActiveSheet.Rows(ActiveCell.Row
1).Columns(1).Value = ""
ActiveSheet.Rows(10).Columns(3).Value
"9999"
avoidloop = True
End If
Case 2
Case 3
If ActiveCell.Row = 3 Then
If Target < "" Then SAVE_DATA (Target)
End If

Case Else
End Select
End If
End If
errorhandler:
End Sub

Where would I place that 8 digit sorter so that it would do what I wan
it to do

--
Lil Pu
-----------------------------------------------------------------------
Lil Pun's Profile: http://www.excelforum.com/member.php...fo&userid=3384
View this thread: http://www.excelforum.com/showthread.php?threadid=54975

Ignoring Certain Characters In a Cell Using VBA
 

OK, I I think I tried what was recommended above he


Private Sub Worksheet_Activate()
avoidloop = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errorhandler
If avoidloop And Trim(Target) < "" Then
If Target = "1" Then
Range("C2").Select
Application.SendKeys "{F2}"
Else
Select Case (ActiveCell.Column)
Case 1
avoidloop = False
If
UCase(Left(ActiveSheet.Rows(2).Columns(1).Value, 8)) =
UCase(Left(Target, 8)) Then
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(1).Value = Target
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(2).Value = ""
avoidloop = True
Else
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(2).Value = Target
ActiveSheet.Rows(ActiveCell.Row -
1).Columns(1).Value = ""
ActiveSheet.Rows(10).Columns(3).Value =
"9999"
avoidloop = True
End If
Case 2
Case 3
If ActiveCell.Row = 3 Then
If Target < "" Then SAVE_DATA (Target)
End If

Case Else
End Select
End If
End If
errorhandler:
End Sub



Is this it?


--
Lil Pun
------------------------------------------------------------------------
Lil Pun's Profile: http://www.excelforum.com/member.php...o&userid=33840
View this thread: http://www.excelforum.com/showthread...hreadid=549758