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.

Steve · Accepted Answer · 2015-04-19 13:12:02Z

4

'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

answered Apr 19, 2015 at 13:12

Steve

1,6482 gold badges19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

slayernoah · Accepted Answer · 2015-04-19 19:14:51Z

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.

edited Apr 19, 2015 at 19:14

slayernoah

4,51012 gold badges45 silver badges76 bronze badges

answered Apr 19, 2015 at 13:18

JFS

3,1423 gold badges40 silver badges52 bronze badges

Collectives™ on Stack Overflow

Runtime error while using variables in VBA

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related