CODEWITHSUNDEEP
vbaexcel
cg529d
cg529d

Reputation: 29

Expected End Sub in VBA

I have just started to use VBA and know very little. Guessing the error has to do with multiple subs and not declaring and defining them properly:

In the code snippet I am trying to allow the user to enter amounts of "Apples and Pears" and then be able to calculate the total and export it back into the excel sheet.

Any help would be most appreciated - thanks!

Private Sub calculateButton_Click()
Private Sub calculation()


Dim applesCalculation As String
Dim pearsCalculation As String
Dim newRow

applesCalculation = applesTextbox.Text
pearsCalculation = pearsTextbox.Text

calculation = applesCalculation + pearsCalculation

newRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

End Sub

Upvotes: 0

Views: 257

Answers (2)

kolcinx
kolcinx

Reputation: 2233

The error is because you have 2 Sub at the top and just 1 End Sub at the bottom of your code.
If you want to run your code after button is clicked, delete the Private Sub calculation() line from your code.
Another thing is that you are trying to concatenate strings, instead of adding number of apples and pears.

I think that instead of us trying to fix your code, it would be better if you looked at some VBA tutorials.

Links to the tutorials

  1. http://www.excelfunctions.net/Excel-VBA-Tutorial.html
  2. https://www.youtube.com/user/ExcelVbaIsFun
  3. https://www.youtube.com/watch?v=ABXPb0qnKUY

Other sources

  1. https://msdn.microsoft.com/en-us/library/office/ff194068.aspx
  2. VBA Help Feature (F1)

Of course we are willing to help you and will gladly do, but these are really basics you are missing. I encourage you to come back later, if you are still stuck.

Upvotes: 2

CaBieberach
CaBieberach

Reputation: 1768

Analize the following tips:

Private Sub calculateButton_Click()
   calculation
end Sub

Private Sub calculation()
   Dim applesCalculation As Long
   Dim pearsCalculation As Long
   Dim newRow

   applesCalculation = val(applesTextbox.Text)
   pearsCalculation = val(pearsTextbox.Text)

   calculation = applesCalculation + pearsCalculation

   newRow = cells(rows.count,1).end(xlup).row + 1
   Thisworkbook.Worksheets(1).Range("A" & newRow).value= newRow

End Sub

Upvotes: 1

Related Questions