none
VBA Inputbox for multiple values in one column RRS feed

  • Question

  • Hello Developers!

    I am working on writing a code for an input box. Basically, it will ask the user for a reference number or multiple reference numbers and will be entered beginning A2.  Example, i want to enter the following from A2 to A4.

    ABC

    DEF

    GHI


    My code works partially correct because instead of entering the references starting A2, it enters everything in A2, instead of A2 to A4. 


    Appreciate your help in advance!

    Dim strEntries As String, v As Variant
        
        strEntries = Application.InputBox("Enter multiple comma separated values. ", "Entries", Type:=2)
        
      
        
        If strEntries = "false" Then Exit Sub   'User canceled
        
        v = Split(strEntries, ",")
        
        If IsArray(v) Then
            Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value = Application.Transpose(v)
            'Range("A1").Resize(UBound(v) + 1).Value = Application.Transpose(v)
        Else
            Range("A2").Value = v
        End If


    Monday, August 5, 2019 9:10 PM

Answers

  • Try this version:

        Dim strEntries As String, v As Variant
        strEntries = Application.InputBox("Enter multiple comma separated values. ", "Entries", Type:=2)
        If strEntries = "False" Then Exit Sub   'User canceled
        v = Split(strEntries, ",")
        Range("A2").Resize(UBound(v) + 1).Value = Application.Transpose(v)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by IamJackie Monday, August 5, 2019 10:08 PM
    Monday, August 5, 2019 9:23 PM

All replies

  • Try this version:

        Dim strEntries As String, v As Variant
        strEntries = Application.InputBox("Enter multiple comma separated values. ", "Entries", Type:=2)
        If strEntries = "False" Then Exit Sub   'User canceled
        v = Split(strEntries, ",")
        Range("A2").Resize(UBound(v) + 1).Value = Application.Transpose(v)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by IamJackie Monday, August 5, 2019 10:08 PM
    Monday, August 5, 2019 9:23 PM
  • Thank you so much! It worked!
    Monday, August 5, 2019 10:09 PM