none
Identify FontStyle in an Excel worksheet and generate a string? RRS feed

  • Question

  • I am new to VBA macro coding in Excel. I'm trying to generate a macro that will identify the font style of a string within one column and then generate a string in the corresponding row of another column.

    A sample of data I have is here:

    Representative
    State
    District
    Ackerman, Gary L. NY 7th
    Alexander, Bill AR 1st
    Anderson, Glenn M. CA 32nd
    Andrews, Michael A. TX 25th
    Andrews, Robert E. NJ 1st
    Annunzio, Frank IL 11th
    Anthony, Beryl Jr. AR 4th
    Applegate, Douglas OH 18th
    Archer, Bill TX 7th
    Armey, Richard K. TX 26th


    A name in italics in column one represents the person's political party affiliation. If the name is not in italics then it represents another party affiliation. Finally, if the name is underlined (not shown) it represents yet another party affiliation.

    I would like to create a simple macro that creates a fourth column. If the person's name is in italics then I would like to create a simple string "D" (to indicate party) in the same corresponding row of the fourth column.

    Here is a code that I created, but it does not work.

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

    Sub Party()
    Dim Row As Integer
    Dim Name As String
    Dim Affil As String
    '
    ' Party Macro
    '
    For Row = 2 To 442

    'Assign the party affiliation based upon the formatted name
    Name = ActiveSheet.Cells(Row,1)

    Select Case Range("A2:A11").Font.FontStyle
        Case "Regular"
            Affil = "R"
        Case "Underline"
            Affil = "I"
        Case Else
            Affil = "D"
    End Select

    ' Write the party affiliations
    ActiveSheet.Cells(Row, 4) = Affil

    Next Row

    End Sub

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

    Does someone have another suggestion for how to code this? I appreciate your help in advance.

    • Moved by Mark Liu-lxfModerator Thursday, March 8, 2012 6:24 AM VBA issue (From:Visual Basic Express Edition)
    Wednesday, March 7, 2012 3:33 AM

Answers

  • Try this. Underline is a property of the Font object, the FontStyle property is somewhat limited.

    Dim cell As Range
    Dim Affil As String
    For Each cell In Range("A2:A11")
      If cell.Font.Underline <> xlUnderlineStyleNone Then
        Affil = "I"
      ElseIf cell.Font.Italic Then
        Affil = "D"
      Else
        Affil = "R"
      End If
      cell.Offset(0, 4) = Affil
    Next cell


    Ed Ferrero
    www.edferrero.com

    Friday, March 9, 2012 1:58 AM

All replies

  • Try the forum for VBA this is the forum for VB Express (VBE)

    http://social.msdn.microsoft.com/Forums/en-US/isvvba


    Success
    Cor

    Wednesday, March 7, 2012 10:55 AM
  • Hi there,

    You can do something like this, see below code.

    Note: I can't get the Underlined Fontstyle being read from a Range, somehow it only read "Regular".

    So when you change this to Bold, for example that will work.

    Sub test()
    
    Dim rng As Range
    Dim rng2 As Range
    
    Set rng = Range("A5:A22")
    Set rng2 = Range("B5:B22")
    
    For i = 1 To rng.Cells.Count
    
     Select Case rng(i).Font.FontStyle
    
         Case Is = "Regular"
    
           rng2(i).Value = "R"
    
         Case Is = "Italic"
    
           rng2(i).Value = "D"
    
       Case Is = "Bold"
    
           rng2(i).Value = "I"
    
       Case Else
       
         rng2(i).Value = "Undefined"
    
    End Select
    
    Next i
    
    
    End Sub

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Thursday, March 8, 2012 11:26 PM
    Moderator
  • Try this. Underline is a property of the Font object, the FontStyle property is somewhat limited.

    Dim cell As Range
    Dim Affil As String
    For Each cell In Range("A2:A11")
      If cell.Font.Underline <> xlUnderlineStyleNone Then
        Affil = "I"
      ElseIf cell.Font.Italic Then
        Affil = "D"
      Else
        Affil = "R"
      End If
      cell.Offset(0, 4) = Affil
    Next cell


    Ed Ferrero
    www.edferrero.com

    Friday, March 9, 2012 1:58 AM