VBA issue - storing data in array from inputbox

Question

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

Try .. arr6 = split(sn&","&em_ID&","&name&","&dept&","&hostname&","&loc, ",") .. this will give you 0 based arr6 — Naresh
– Naresh, Commented Jun 27, 2021 at 15:10
You defined arr6 as a string. You need to define it as an array — Michael Vine
– Michael Vine, Commented Jun 27, 2021 at 15:10
Yes.. Dim arr6() As String will give you string type array. — Naresh
– Naresh, Commented Jun 27, 2021 at 15:19

Алексей Р · Accepted Answer · 2021-06-27 19:08:03Z

3

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

answered Jun 27, 2021 at 19:08

Алексей Р

7,6272 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FaneDuru Over a year ago

Nice! I like compact pieces of code... Voted up.

Gary's Student Over a year ago

.......................................Super clever!

Naresh · Accepted Answer · 2021-06-30 07:22:12Z

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

Gary's Student · Accepted Answer · 2021-06-27 15:28:02Z

1

We need to:

define the array
fill the array
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

answered Jun 27, 2021 at 15:28

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

3 Comments

Naresh Over a year ago

No need to even create variables .. we can directly input values in array as arr(1) = InputBox("Enter Laptop's Serial") and arr(6) = InputBox("Enter office location") and arr(5) = "VN" & arr(6) & arr(1)

Gary's Student Over a year ago

@Naresh Good Comment You are completely correct!

thangvc91 Over a year ago

thank you all for your help :) . here is the best place for me can learn alot about programing skills.

Collectives™ on Stack Overflow

VBA issue - storing data in array from inputbox

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related