Query on to how to further improve this VBA

[Hottest Fudge]

Background: At current I am working on a extremely user friendly database for old people. They input data into a "master database" then excel pulls out information from this database to create various information sheets that can be printed off.

To input/update data I am attempting to use VBA (first time). At current I have set up a few question that the operator will be asked to enter upon pressing a button. The code at present does what it is intended to but I would like remove the go to line 10 function as I have heard this is bad practice. Is there any other way of doing this? Perhaps loops and stuff, but I am very new to this so an explanation/link to explanation would be very well received.

Thank you for reading!

Cheers,

Hottest Fudge

Sub NewSubstance()
Sheets("Input Sheet").Select
Dim reFerence As String
Dim NewRow As Variant
Dim Material As String
NewRow = Range("A1").End(xlDown).Row + 1
10: Material = InputBox("What is the name of the substance? ", , ("e.g. Dissolved Actylene"))
Range("B" & NewRow).Value = Material
If Len(Material) = 0 Or Material = ("e.g. Dissolved Actylene") Then
Range("B" & NewRow).Value = ("")
Exit Sub
End If
reFerence = InputBox(" What is the reference number of this substance? ", , ("e.g. AER_....."))
Range("A" & NewRow).Value = reFerence
If Len(reFerence) = 0 Or reFerence = ("e.g. AER_.....") Then
Range("A" & NewRow).Value = ("")
Exit Sub
End If
If reFerence = ("back") Then
Range("A" & NewRow).Value = ("")
GoTo 10
End If
End Sub

 

[Andrew Poulsom]

Sorry I don't understand. How would one determine which column to use?

 

[Hottest Fudge]

At the moment the spreadsheet with this raw data on it has different questions running along the top row, the first question is "what is the reference" (in cell A1). Column A has all the different references in this column that are different substances. Each column has already been determine as it has a respective question. Sorry I forgot to mention this I will remember for next time. The code in my initial statement works for my need I was looking for a more elegant solution.

 

[Fluff]

Thanks Fluff would there be any way of adding in different substances as well. To make it more of a database of many different substances?

If you mean questions, then simply keep adding the information in the next available row on the Qs sheet, making sure that there are no blank rows.