+ Reply to Thread
Results 1 to 4 of 4

check if cell only has letter "M", not "MS" or others

Hybrid View

  1. #1
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    check if cell only has letter "M", not "MS" or others

    currently i use code to check for values that start with certain strings, but problem arouse when i was looking for cells that started with the letter "M". I have multiple instances of the letter "M" with can be followed by other letters. can i find only the one cell that starts with only the letter m followed by no other letters? An Example of two cells would be
    MS301445
    MP113555
    M443229 this one being the one i want.
    the numbers change all the time but the format of it does not.
    
    Dim CriteriaC As String 'In this case CriteriaC is equal to "PE"
    'some code
    If Left(Range("A" & i).Value, 2) = CriteriaC Then
            Range("A" & i & ":D" & i & "").Copy
    'some code
    End Sub

  2. #2
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: check if cell only has letter "M", not "MS" or others

    For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If Left(c.Value, 1) = "M" And IsNumeric(Mid(c.Value, 2, 1)) Then
            'Your code here
        End If
    Next

  3. #3
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: check if cell only has letter "M", not "MS" or others

    ok i tested code and i had to make some changes. why wont it do the code following the test? i bet its right in front of me...
    Sub DeviceCordLabels()
    Dim MyCount As Integer
    Dim DCL As Integer
    Dim CriteriaA As String
    Dim CriteriaB As String
    Dim CriteriaC As String
    Dim CriteriaD As String
    
    
    DCL = 1
    MyCount = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    
        For i = 6 To MyCount Step 1
            Sheets("DEVICE LABELS").Select
            
            CriteriaA = Left(Range("A" & i).Value, 3)
            If CriteriaA = "PRX" Then
                Range("A" & i & ":D" & i & "").Copy
                Sheets("DEVICE CORD").Select
                Range("A" & DCL).Select
                ActiveSheet.Paste
                DCL = DCL + 1
            End If
            Sheets("DEVICE LABELS").Select
            CriteriaB = Left(Range("A" & i).Value, 2)
            If CriteriaB = "PE" Then
                Range("A" & i & ":D" & i & "").Copy
                Sheets("DEVICE CORD").Select
                Range("A" & DCL).Select
                ActiveSheet.Paste
                DCL = DCL + 1
            End If
            Sheets("DEVICE LABELS").Select
            CriteriaC = Left(Range("A" & i).Value, 1)
            test = IsNumeric(Mid(Range("A" & i).Value, 2, 1))
            If CriteriaC = "M" And test = 1 Then                       'both values could be true but it wont run code.
                Range("A" & i & ":D" & i & "").Copy
                Sheets("DEVICE CORD").Select
                Range("A" & DCL).Select
                ActiveSheet.Paste
                DCL = DCL + 1
            End If
            Sheets("DEVICE LABELS").Select
            CriteriaD = Left(Range("A" & i).Value, 2)
            If CriteriaD = "PS" Then
                Range("A" & i & ":D" & i & "").Copy
                Sheets("DEVICE CORD").Select
                Range("A" & DCL).Select
                ActiveSheet.Paste
                DCL = DCL + 1
            End If
        Next i
    
    End Sub

  4. #4
    Registered User
    Join Date
    01-31-2013
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: check if cell only has letter "M", not "MS" or others

    wow... 1 does not equal true. I knew it was in front of me!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1