VLOOKUP Trouble w/ data from Multiline Textbox

[szymagic]

So I need some help with some VBA code. I am pretty much brand new to VBA, let alone coding, and you fine people of MrExcel have been my saviors over the past week.

The goal of my macro is to grab a variable amount of ID numbers (anywhere from 1 – 30) and to paste them in a column of another worksheet that is tied to a bunch of VLOOKUP formulas. My problem comes from the fact that when the IDs are posted in this column the Vlookup function doesn’t work. I have a feeling there is a carriage return, or something of that nature, attached to these numbers (using Ctrl + F and then Ctrl + J or Alt 0010 did not return anything). The IDs that this macro is grabbing come from a userform multiline textbox that the user pastes IDs into, then those IDs are pasted into the next available row and then split from one cell into several. The code for the userform I have is a little messy, but is as follows:

Private Sub ok_Click()

Range("'Report'!AL85").Select
ActiveCell.End(xlToRight).Select
lastcolumn = ActiveCell.Column
Cells(85, lastcolumn + 1) = txtpdp.Value
ActiveCell.End(xlToRight).Select

'Code found on Internet
    Dim Str As String, a
    Dim cnt As Integer
    Dim w()

             
    Str = txtpdp.Value
    a = Chr(10)
    cnt = UBound(Split(Str, a))
     
    ReDim w(1 To cnt + 1, 1 To 1)
     
    For i = 0 To cnt
        w(i + 1, 1) = Split(Str, Chr(10))(i)
    Next i
    'ActiveSheet.Range("AO85").Resize(i, 1) = w
    Worksheets("Report").Activate
    ActiveSheet.Range("AN85").End(xlToRight).Resize(i, 1) = w
    
Worksheets("Data Addition").Activate

    
Unload Me

End Sub

Thank you for any help or guidance on this issue! Sorry if it is a little difficult to understand what is going on, please feel free to ask any clarifying questions! Thanks again!

 

[01-7700]

So I need some help with some VBA code. I am pretty much brand new to VBA, let alone coding, and you fine people of MrExcel have been my saviors over the past week.

The goal of my macro is to grab a variable amount of ID numbers (anywhere from 1 – 30) and to paste them in a column of another worksheet that is tied to a bunch of VLOOKUP formulas. My problem comes from the fact that when the IDs are posted in this column the Vlookup function doesn’t work. I have a feeling there is a carriage return, or something of that nature, attached to these numbers (using Ctrl + F and then Ctrl + J or Alt 0010 did not return anything). The IDs that this macro is grabbing come from a userform multiline textbox that the user pastes IDs into, then those IDs are pasted into the next available row and then split from one cell into several. The code for the userform I have is a little messy, but is as follows:

Private Sub ok_Click()

Range("'Report'!AL85").Select
ActiveCell.End(xlToRight).Select
lastcolumn = ActiveCell.Column
Cells(85, lastcolumn + 1) = txtpdp.Value
ActiveCell.End(xlToRight).Select

'Code found on Internet
    Dim Str As String, a
    Dim cnt As Integer
    Dim w()

             
    Str = txtpdp.Value
    a = Chr(10)
    cnt = UBound(Split(Str, a))
     
    ReDim w(1 To cnt + 1, 1 To 1)
     
    For i = 0 To cnt
        w(i + 1, 1) = Split(Str, Chr(10))(i)
    Next i
    'ActiveSheet.Range("AO85").Resize(i, 1) = w
    Worksheets("Report").Activate
    ActiveSheet.Range("AN85").End(xlToRight).Resize(i, 1) = w
    
Worksheets("Data Addition").Activate

    
Unload Me

End Sub

Thank you for any help or guidance on this issue! Sorry if it is a little difficult to understand what is going on, please feel free to ask any clarifying questions! Thanks again!

vlookup only works on a range that is sorted ascending

 

[szymagic]

Yup, the range is sorted correctly

 

[01-7700]

Yup, the range is sorted correctly

Try using the trim() function to eliminate potential CR problems

 

[szymagic]

Try using the trim() function to eliminate potential CR problems

Sadly, that did not work

 

[szymagic]

Also clean() did not work either

 

[szymagic]

a gauntlet of clean() and then fixed() and value() works..

Thanks for the right direction!