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]

Welcome to MrExcel.

Maybe:

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
    Do
        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 Do
        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 Do
        End If
        If reFerence = "back" Then
            Range("A" & NewRow).Value = ""
        Else
            Exit Do
        End If
    Loop
End Sub

 

[Hottest Fudge]

Thank you for you prompt response! Your code works for what I asked, but what I wanted was a little different. I am sorry for any confusion and my rather unlettered question.

This code is needing to be ran for about 100 questions all together. I basically need a "back" button that allows the operator to go to the previous question. In this code when I add more questions and type "back" it goes back to the initial question, I would like it to go back to just the previous question. Is there a way to go back to the previous input box without using goto line etc.

 

[Andrew Poulsom]

You can put another Do ... Loop inside the existing one. But where do you want to put the subsequent answers?

 

[Hottest Fudge]

Subsequent answers would be put on the same row in a different column. For example A2, A3, A4 ect.. Some of the questions are not in order of ascending column number though (like the first two).

 

[Andrew Poulsom]

But your code is putting the reference in column A. Please describe in words what you would like the procedure to do.

 

[Hottest Fudge]

I have previously explained what I want the procedure to do. I think the confusion is coming in because the questions asked in the input boxes are not entirely in order of ascending column number (which I have said before).

 

[Fluff]

Hi
If you have approx 100 questions, rather than hard coding them, how about putting them on a new sheet & looping through that?
For instance with a sheet called Qs
Col A is the Question
Col B is the Example
Col C is the Column Number for where the answer goes.

[TABLE="width: 595"]
<tbody>[TR]
[TD]What is the name of the substance?[/TD]
[TD]e.g. Dissolved Actylene[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]What is the reference number of this substance?[/TD]
[TD]e.g. AER_.....[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]What is the Chemical Formula?[/TD]
[TD]e.g. H2SO4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Is it Flammable?[/TD]
[TD]e.g. Yes or No[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
And then you could try this

Sub NewSubstance()
    
    Dim NxtRw As Long
    Dim MyRef As String
    Dim iCnt As Long
    Dim InSht As Worksheet
    Dim QRws As Integer
    Dim Quest As String
    Dim Xampl As String
    Dim ColNum As Integer
    
    Set InSht = Sheets("Input Sheet")
    NxtRw = InSht.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).row
    With Sheets("Qs")
        QRws = .Range("A1").CurrentRegion.Rows.Count

        For iCnt = 1 To QRws
            Quest = .Range("A" & iCnt)
            Xampl = .Range("B" & iCnt)
            ColNum = .Range("C" & iCnt)
            MyRef = InputBox(Quest, , Xampl)
            If Len(MyRef) = 0 Or MyRef = Xampl Then
                InSht.Cells(NxtRw, ColNum) = ""
                Exit Sub
            ElseIf MyRef = "back" Then
                InSht.Cells(NxtRw, ColNum) = ""
                If iCnt = 1 Then Exit Sub
                iCnt = iCnt - 2
            Else
                InSht.Cells(NxtRw, ColNum) = MyRef
            End If
        Next iCnt
    End With

 

[Hottest Fudge]

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?

 

[Hottest Fudge]

But your code is putting the reference in column A. Please describe in words what you would like the procedure to do.

Sorry Andrew I meant "Subsequent answers would be put on the same row in a different column. For example B2,A2,C2 ect)