Having issues with Index MATCH.

[jmpatrick]

I use this function to split comment text to multiple rows:

Function GetComment(cl As Range) As Variant
   GetComment = Application.Transpose(Split(cl.Comment.Text, vbLf))
End Function

I paste the comment from a cell on another sheet to cell B6. The comment text runs from A6:A11 using the code above. In G7 I have a formula that matches the value in A7 with data from row A on the Options sheet and returns the value from column B:

Unfortunately, G7 will not populate. I figured it was some sort of text format issue so I tested it by copying A7 on the SendFloorRequest sheet (top) and pasting it on A11 on the Options sheet (bottom). It then worked correctly. Not sure what is causing this.

 

[jmpatrick]

I did check for leading and trailing spaces and there weren't any.

 

[jmpatrick]

Well, now it appears that a trailing space IS causing my grief! Here's the code that creates the Comment:

Private Sub cmdAddComment_Click()
On Error Resume Next
 Dim com As String
 com = OptionList.Text

    Dim vCellValue As Variant
    Dim sText As String
  
    vCellValue = ActiveCell.Value
    If IsNumeric(vCellValue) Then
        vCellValue = CDbl(vCellValue)
    End If
      
    sText = Application.UserName & ":" & vbCrLf
    sText = sText & com
  
    With ActiveCell
        .ClearComments
        With .AddComment
            .Text sText
            With .Shape
                .TextFrame.Characters(1, InStr(sText, ":")).Font.Bold = True
                .Width = 180
                .Height = 60
                With .TextFrame.Characters.Font
                   .Name = "Tahoma"
                   .Size = 12
                End With
                With .TextFrame
                   .AutoSize = True
                End With
            End With
        End With
        
        On Error GoTo 0
        
    End With
  
End Sub

Something in there is adding a space after each line of text and before the carriage return.

 

[Fluff]

Try using

sText = Application.UserName & ":" & vbLf