Macro to add data to a table based on questions

[ghp2017]

Hello,

So far I have this but I can not get it use the next line inside of a table. It always uses the next row after the end of the table. Any thoughts?

strName = InputBox("What is the Estimate #")
strName1 = InputBox("What is the Prospects Name?")
strName2 = InputBox("What is the Prospects Address?")
strName3 = InputBox("What is the Prospects City?")
strName4 = InputBox("What is the Prospects Phone Number?")
strName5 = InputBox("Are they an existing customer?")
strName6 = InputBox("Where did they hear about us?")
strName7 = InputBox("What type of job is this?")
strName8 = InputBox("What is the value of the quote?")
strName9 = InputBox("What is the Date (MM/DD/YY) the Estimate was completed?")
NextEmptyRow = Sheets("Estimates").Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
Sheets("Estimates").Cells(NextEmptyRow, "A").Value = strName
Sheets("Estimates").Cells(NextEmptyRow, "B").Value = strName1
Sheets("Estimates").Cells(NextEmptyRow, "C").Value = strName2
Sheets("Estimates").Cells(NextEmptyRow, "D").Value = strName3
Sheets("Estimates").Cells(NextEmptyRow, "E").Value = strName4
Sheets("Estimates").Cells(NextEmptyRow, "F").Value = strName5
Sheets("Estimates").Cells(NextEmptyRow, "G").Value = strName6
Sheets("Estimates").Cells(NextEmptyRow, "H").Value = strName7
Sheets("Estimates").Cells(NextEmptyRow, "I").Value = strName8
Sheets("Estimates").Cells(NextEmptyRow, "J").Value = strName9
​

 

[igold]

Are you really trying to insert into a table or into a range of data. If it is a table then you may want to look at this site for a guide to working with tables..

https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

 

[ghp2017]

Are you really trying to insert into a table or into a range of data. If it is a table then you may want to look at this site for a guide to working with tables..

https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

---

Thanks. I am using the Insert > Table function so I believe it's a Table. Looked at the site but I still can't figure it out.

 

[igold]

Well if you click on a cell in the "Table" and look at the ribbon, if it is highlighted on "Table Tools" then you know you are in a table. With that, click on "design", and way over on the left you will see the table name. If you could provide that and just a little more information about what you're trying accomplish it would be helpful.

From your code it looks like you want to put the responses to your input boxes in a single row across the table. Is it that each time you run the code with a new set of response you want a row added to the current table with the new responses?

 

[ghp2017]

Well if you click on a cell in the "Table" and look at the ribbon, if it is highlighted on "Table Tools" then you know you are in a table. With that, click on "design", and way over on the left you will see the table name. If you could provide that and just a little more information about what you're trying accomplish it would be helpful.

From your code it looks like you want to put the responses to your input boxes in a single row across the table. Is it that each time you run the code with a new set of response you want a row added to the current table with the new responses?

---

Table Name: Table1

From your code it looks like you want to put the responses to your input boxes in a single row across the table. Is it that each time you run the code with a new set of response you want a row added to the current table with the new responses? Yes

Thanks!!

 

[igold]

Does this get you any closer to where you want to be...

Sub test()
    
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("Table1")


    Dim rw As Long, i As Long
    strname = InputBox("What is the Estimate #")
    strName1 = InputBox("What is the Prospects Name?")
    strName2 = InputBox("What is the Prospects Address?")
    strName3 = InputBox("What is the Prospects City?")
    strName4 = InputBox("What is the Prospects Phone Number?")
    strName5 = InputBox("Are they an existing customer?")
    strName6 = InputBox("Where did they hear about us?")
    strName7 = InputBox("What type of job is this?")
    strName8 = InputBox("What is the value of the quote?")
    strName9 = InputBox("What is the Date (MM/DD/YY) the Estimate was completed?")
    
    tbl.ListRows.Add AlwaysInsert:=True
    rw = tbl.ListRows.Count
    i = 1
    tbl.DataBodyRange(rw, i) = strname
    For i = 2 To 10
        tbl.DataBodyRange(rw, i) = strname & i - 1
    Next
            
End Sub

 

[ghp2017]

It adds the information to the next row in the table but now it just adds numbers and the "answers/responses" to the questions.

 

[igold]

Is this better...

Sub test()
    
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("Table1")
    Dim strname(0 To 9)


    Dim rw As Long, i As Long
    strname(0) = InputBox("What is the Estimate #")
    strname(1) = InputBox("What is the Prospects Name?")
    strname(2) = InputBox("What is the Prospects Address?")
    strname(3) = InputBox("What is the Prospects City?")
    strname(4) = InputBox("What is the Prospects Phone Number?")
    strname(5) = InputBox("Are they an existing customer?")
    strname(6) = InputBox("Where did they hear about us?")
    strname(7) = InputBox("What type of job is this?")
    strname(8) = InputBox("What is the value of the quote?")
    strname(9) = InputBox("What is the Date (MM/DD/YY) the Estimate was completed?")
    
    tbl.ListRows.Add AlwaysInsert:=True
    rw = tbl.ListRows.Count
    For i = 1 To 10
        tbl.DataBodyRange(rw, i) = strname(i - 1)
    Next
            
End Sub

 

[ghp2017]

Is this better...

Sub test()
    
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("Table1")
    Dim strname(0 To 9)


    Dim rw As Long, i As Long
    strname(0) = InputBox("What is the Estimate #")
    strname(1) = InputBox("What is the Prospects Name?")
    strname(2) = InputBox("What is the Prospects Address?")
    strname(3) = InputBox("What is the Prospects City?")
    strname(4) = InputBox("What is the Prospects Phone Number?")
    strname(5) = InputBox("Are they an existing customer?")
    strname(6) = InputBox("Where did they hear about us?")
    strname(7) = InputBox("What type of job is this?")
    strname(8) = InputBox("What is the value of the quote?")
    strname(9) = InputBox("What is the Date (MM/DD/YY) the Estimate was completed?")
    
    tbl.ListRows.Add AlwaysInsert:=True
    rw = tbl.ListRows.Count
    For i = 1 To 10
        tbl.DataBodyRange(rw, i) = strname(i - 1)
    Next
            
End Sub

---

This is perfect THANK YOU!!!

 

[igold]

Happy to help. Thanks for the feedback!