Runtime error while using variables in VBA

Question

This simple line of code is throwing up error:

Application or Object defined error.

Sub vartest()
movie_name = "TITANIC"
Worksheets("Sheet1").Activate

Range("b1").End(xlDown).Offset(1, 0).Select
Active.Value = movie_name
MsgBox movie_name & " is the Movie"

Please help.

Answer 1

'Active' is not an acceptable object...

You would need something like ActiveCell

ActiveCell.Value = movie_name

Though you may be better off not selecting at all :

Sub vartest()
    movie_name = "TITANIC"
    Worksheets("Sheet1").Range("b1").End(xlDown).Offset(1, 0).Value = movie_name
    MsgBox movie_name & " is the Movie"
End Sub

Answer 2

You need to change select and activate:

Sub vartest()

 Dim movie_name As String: movie_name = "TITANIC"
 Worksheets("Sheet1").Activate

 Range("b1").End(xlDown).Offset(1, 0).Activate
 Selection.Value = movie_name
 MsgBox movie_name & " is the movie"

End Sub

But try too avoid activate and select as already recommended. It slows down your code and it is not needed in most cases.