Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: Parse Name from one sheet to another

    Ok, now that I have a flat forehead from repeatedly banging it against the desk, I will ask for help from those who really know what they're doing!
    I'm trying to parse First Name out of column B in "PayOriginal" sheet that has "Firstname Lastname" into a column B in the ActiveSheet (Sheet1). Both sheets in the same workbook. The VB compiler keeps saying "Sub or Function not defined" when it hits the SEARCH(...) function. The VBA library is listed as present in the References window.

    Sub ParseFirst()

    Dim IsLastRow, counter As String

    IsLastRow = Worksheets("PayOriginal").UsedRange.Rows.Count

    For counter = 2 To IsLastRow

    ActiveSheet.Range("B" & counter).Value = Left(Worksheets("PayOriginal").Range("B" & counter).Value, Search(" ", Worksheets("PayOriginal").Range("B" & counter).Value, 1) - 1)

    Next
    End Sub

    The logic is Microsoft's own:
    http://office.microsoft.com/en-us/ex...498501033.aspx

    What "totally newbie" thing am I doing?
    If I can get this to work, parsing the Middle Initial (when there is one) and Last Name should follow. sigh.
    Thanks in advance for any advice you all can give.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by jonllaa
    Ok, now that I have a flat forehead from repeatedly banging it against the desk, I will ask for help from those who really know what they're doing!
    I'm trying to parse First Name out of column B in "PayOriginal" sheet that has "Firstname Lastname" into a column B in the ActiveSheet (Sheet1). Both sheets in the same workbook. The VB compiler keeps saying "Sub or Function not defined" when it hits the SEARCH(...) function. The VBA library is listed as present in the References window.

    Sub ParseFirst()

    Dim IsLastRow, counter As String

    IsLastRow = Worksheets("PayOriginal").UsedRange.Rows.Count

    For counter = 2 To IsLastRow

    ActiveSheet.Range("B" & counter).Value = Left(Worksheets("PayOriginal").Range("B" & counter).Value, Search(" ", Worksheets("PayOriginal").Range("B" & counter).Value, 1) - 1)

    Next
    End Sub

    The logic is Microsoft's own:
    http://office.microsoft.com/en-us/ex...498501033.aspx

    What "totally newbie" thing am I doing?
    If I can get this to work, parsing the Middle Initial (when there is one) and Last Name should follow. sigh.
    Thanks in advance for any advice you all can give.
    Hi

    Just a little confused as to what precisely you are trying to achieve.

    If you are trying to enter a formula in to the cells then I think this will do it
    Code:
    Sub ParseFirst()
    
        Dim IsLastRow, counter As Integer
        
        IsLastRow = Worksheets("PayOriginal").UsedRange.Rows.count
        
        For counter = 2 To IsLastRow
            ActiveSheet.Range("B" & counter & "") = "=LEFT(PayOriginal!B" & counter & ",SEARCH("" "",PayOriginal!B" & counter & ",1)-1)"
        Next
    End Sub
    If tou are trying to enter the actual name then this should do it
    Code:
    Sub ParseFirst()
    
        Dim IsLastRow, counter As Integer
        
        IsLastRow = Worksheets("PayOriginal").UsedRange.Rows.count
        
        For counter = 2 To IsLastRow
        
        ActiveSheet.Range("B" & counter) = Left(Worksheets("PayOriginal").Range("B" & counter), InStr(Worksheets("PayOriginal").Range("B" & counter), " ") - 1)
        
        Next
    End Sub
    The Microsoft site you referred to was an illustration of worksheet formulas not VBA.

    You will also notice I changed the valuables to integers.

    However, you will (may) need to address the issue of no space in the search text with both these solutions (if there is a possibility of this occurring?).


    HTH


    MTB

  3. #3
    Join Date
    Aug 2009
    Posts
    2

    Smile Thanks for the help and the final ugly solution

    Hi Mike, Thanks alot for pointing me in the right direction. Sorry for not being clear -- I did want to parse the actual name parts into separate columns. I haven't had to program in VBA for quite a few years and it's changed a bit (no surprise). The real nut to crack was the fact that my input data is not consistently formatted. Some have no middle initial (and so only 1 space in the name), some have one letter MidInit, some have letter + period, some have the MidIniti appended the the first or last name with an underscore, etc. Getting the first name was no problem, but getting the MidInit correctly and then the last name correctly was more challenging. Thanks to your help, here is the ugly final solution. You have the run the MidInit subroutine before you run the LastName sub in order to get the last name out correctly.

    Code:
    Dim IsLastRow, counter, MiCount As Integer
    Dim MiChar, MiLastChar, LastCellAddress As String
    
    Sub ParseFirst()
    
    IsLastRow = ActiveSheet.UsedRange.Rows.Count
    
    For counter = 2 To IsLastRow
    
    ' Parse out first name from Column B PayOriginal sheet into Sheet1.Column B
    ActiveSheet.Range("D" & counter).Value = Left(ActiveSheet.Range("B" & counter).Value, InStr(ActiveSheet.Range("B" & counter), " ") - 1)
    
    Next
    End Sub
    
    Sub ParseMiddle()
    
    IsLastRow = ActiveSheet.UsedRange.Rows.Count
    
    For counter = 2 To IsLastRow
    
    MiChar = ""
    MiCount = 0
    
    ' Parse out Middle Initial, if there is one, into Sheet1.Column C
    MiChar = Mid(ActiveSheet.Range("B" & counter).Value, InStr(ActiveSheet.Range("B" & counter).Value, " ") + 1, InStr(InStr(ActiveSheet.Range("B" & counter).Value, " ") + 1, ActiveSheet.Range("B" & counter).Value, " ") - InStr(ActiveSheet.Range("B" & counter).Value, " ") - 1)
    
    On Error Resume Next
    MiCount = Len(MiChar)
    
    'Check if the last character in MiChar is a period, if it is, put the whole thing in sheet1.Column C as MI
    MiLastChar = Right(MiChar, 1)
    If MiCount > 1 And Asc(MiLastChar) = 46 Then
       ActiveSheet.Range("E" & counter).Value = MiChar
    ElseIf MiCount = 1 Then
      ActiveSheet.Range("E" & counter).Value = MiChar
    Else
      ActiveSheet.Range("E" & counter).Value = ""
    End If
    
    MiChar = ActiveSheet.Range("E" & counter).Value
    On Error Resume Next
    ActiveSheet.Range("E" & counter).Value = RTrim(MiChar)
    
    Next
    End Sub
    
    Sub ParseLast()
    
    IsLastRow = ActiveSheet.UsedRange.Rows.Count
    
    For counter = 2 To IsLastRow
    
    'If text after 1st space is not MI, then extract Last Name begining from 1st space and put into Sheet1.ColumnA, else extract last name beginning from 2nd space into Sheet1.ColumnA
    If ActiveSheet.Range("E" & counter).Value = "" Then
    ActiveSheet.Range("C" & counter).Value = Right(ActiveSheet.Range("B" & counter).Value, Len(ActiveSheet.Range("B" & counter).Value) - InStr(1, ActiveSheet.Range("B" & counter).Value, " ", vbCompareText) + 1)
    Else
    ActiveSheet.Range("C" & counter).Value = Right(ActiveSheet.Range("B" & counter).Value, Len(ActiveSheet.Range("B" & counter).Value) - InStr(InStr(1, ActiveSheet.Range("B" & counter).Value, " ", vbTextCompare) + 1, ActiveSheet.Range("B" & counter).Value, " ", vbTextCompare))
    End If
    
    MiChar = ActiveSheet.Range("C" & counter).Value
    ActiveSheet.Range("C" & counter).Value = Trim(MiChar)
    
    Next
    End Sub
    I was getting a 5 error when it hit a name with only one space in it, thus the ugly error handling.

    Sigh. It ain't pretty, but it works. Now I have to do a two-sheet compare with lots of convoluted selection criteria. double sigh.

    Thanks again Mike! -- jon

Posting Permissions

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