VBA issue - storing data in array from inputbox

user_11549527 0
Viewed 214

I'm new in VBA , so i would lile to store values from inputbox into array , i wrote the code below , then i tried define a array to store them , i got the issue with my VBA code , could you please help check ? any assist will be appreciated

Sub inpubox()
    Dim sn As String
    Dim em_ID As String
    Dim name As String
    Dim dept As String
    Dim hostname As String
    Dim loc As String
    sn = InputBox("Enter Laptop's Serial")
    em_ID = InputBox("Enter Colleague ID")
    name = InputBox("Enter Colleague Name")
    dept = InputBox("Enter Colleague department")
    loc = InputBox("Enter office location")
    'Xu li hostname
    hostname = "VN" & loc & sn
    sodong = Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1).row
    Dim arr6 As String
    arr6 =(sn,em_id,name,dept,hostname,loc)
           
End Sub

The error also attached array error

vba
3 Answers
Sub ShortCode()
    arr = Array(InputBox("Enter Laptop's Serial"), InputBox("Enter Colleague ID"), _
          InputBox("Enter Colleague Name"), InputBox("Enter Colleague department"), _
          InputBox("Enter office location"))
    Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, UBound(arr) + 1) = arr
End Sub
user_150353140

This direct entry in to the array through inputbox

Option Explicit
Sub inpubox()

Dim arr(1 To 6) As String, sodong as long
    arr(1) = InputBox("Enter Laptop's Serial")
    arr(2) = InputBox("Enter Colleague ID")
    arr(3) = InputBox("Enter Colleague Name")
    arr(4) = InputBox("Enter Colleague department")
    arr(6) = InputBox("Enter office location")
    
    arr(5) = "VN" & arr(6) & arr(1)

sodong = Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheet1.Range("A" & sodong).Resize(1, 6).Value = arr

End Sub

Just to add to brilliant answer from Алексей Р below - - - We can use code like below to overcome the limitation of concatenation for arr(4) position (0 based).

Sub ShortCode()
arr = Array(InputBox("Serial"), InputBox("ID"), InputBox("Name"), _
      InputBox("Department"), "HostName", InputBox("location"))
arr(4) = "VN" & arr(5) & arr(0)
Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Offset(1).Resize(, UBound(arr) + 1) = arr
End Sub
user_98080630

We need to:

  1. define the array
  2. fill the array
  3. store the array in a row of cells

For example:

Sub inpubox()
    Dim sn As String
    Dim em_ID As String
    Dim name As String
    Dim dept As String
    Dim hostname As String
    Dim loc As String
    sn = InputBox("Enter Laptop's Serial")
    em_ID = InputBox("Enter Colleague ID")
    name = InputBox("Enter Colleague Name")
    dept = InputBox("Enter Colleague department")
    loc = InputBox("Enter office location")
    'Xu li hostname
    hostname = "VN" & loc & sn
    sodong = Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    
    Dim arr(1 To 6) As String
    arr(1) = sn
    arr(2) = em_ID
    arr(3) = name
    arr(4) = dept
    arr(5) = hostname
    arr(6) = loc
    
    Sheet1.Range(Cells(sodong, 1), Cells(sodong, 6)) = arr
           
End Sub
user_24746560
Related