VLOOKUP Trouble w/ data from Multiline Textbox

szymagic

Board Regular
Joined
Jul 8, 2016
Messages
61
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:

Code:
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

01-7700

Board Regular
Joined
Nov 7, 2011
Messages
62
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:

Code:
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

Board Regular
Joined
Jul 8, 2016
Messages
61
a gauntlet of clean() and then fixed() and value() works..

Thanks for the right direction!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,981
Messages
5,767,435
Members
425,413
Latest member
ccfam04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top