Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

test first character in each cell to see if alpha

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • test first character in each cell to see if alpha

    I want to be able to go through a range (all in col A) and if the first non-blank character is a Letter, change the font and cell color.

    In excel I can use "Trim" to clear leading blanks, then use "code" to test the ascii value of the first character.

    in VBA I can use the trim and assign cell valuable to a variable, but don't know how to pick off first character in string and test to see if it is an alpha (versus a blank, or a number)

    thanks

  • #2
    Re: test first character in each cell to see if alpha

    The VBA equivalent of CODE is Asc.

    And you can use the Left function like you can on a worksheet.
    Boo!

    Comment


    • #3
      Re: test first character in each cell to see if alpha

      Hi wbsmith,

      THis code will change the cell color to res if the cell is not empty and the first character of the cell value is not numeric.

      Code:
      Sub FindFirstAlpa()
           
          Dim c As Range
      
          For Each c In Range("A1", Range("A65536").End(xlUp))
              If Not IsNumeric(Left(c, 1)) And Not c = Empty Then
                  c.Interior.ColorIndex = 3
              End If
          Next c
      hope this helps

      Bill


      End Sub
      Bill
      Tip: To avoid chasing code always use Option Explicit.

      Comment


      • #4
        Re: test first character in each cell to see if alpha

        You can also do this with native conditional formatting...

        1) Select the entire column A (click the A column header)
        2) Format --> Conditional Formatting --> Formula Is ..
        3) ..
        Code:
        =(NOT(ISBLANK(A1)))*((CODE(UPPER(LEFT(TRIM(A1),1)))>64)*(CODE(UPPER(LEFT(TRIM(A1),1)))<91))
        4)Format as desired.


        HTH
        ___________
        Regards, Zack B. :|: Check out our Knowledge Base!

        Comment


        • #5
          Re: test first character in each cell to see if alpha

          all, great info, thanks. quick general questions
          1) I've read much of vba book by walkenback. Great stuff, but jumps around. Is there a programming reference that presents vba programming in a more comprehensive fashion? There are so many functions, objects, ... that I don't feel comfortable I know how they really function

          2) Mental glitch: what does the "Set" command do and when do you use it instead of an assignment "=" operator

          3) In conditional formatting using formula, how does it know what row to evaluate...that is, to test A1 for row 1, A2 for row 2? what if you wanted A1 to test A2?

          Help here has been invaluable. I was CS guy in college, but years ago now. Lots of fun to be able to play, but only because of the excellent help I can get when I am stuck. tried the cond formatting...am going to try the macro route too.

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X