Not working with offset().value but works with offset.select and then Activecell.value

[mayankgo]

Code isn't working if I use. Why so?

Option Explicit
Sub AddNewFilm() 

Dim NewFilmName As String, NewFilmDate As Date, NewFilmLength As Integer 

NewFilmName = InputBox("Type a new film name") 

NewFilmDate = InputBox("Type the date") 

NewFilmLength = InputBox("Type length in numbers") 

Sheet1.Activate Range("A2").End(xlDown).Offset(1, 0).Select 

ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 

ActiveCell.Offset(0, 1).Select 

ActiveCell.Value = NewFilmName 

ActiveCell.Offset(0, 1).Value = NewFilmDate

ActiveCell.Offset(0, 2).Value = NewFilmLength 

ActiveCell.End(xlToLeft).Offset(1, 0).Select 

MsgBox NewFilmName & " was added to the list"
End Sub

Works if I use

Option Explicit

Sub AddNewFilm()


    Dim NewFilmName As String, NewFilmDate As Date, NewFilmLength As Integer
    
    NewFilmName = InputBox("Type a new film name")
    NewFilmDate = InputBox("Type the date")
    NewFilmLength = InputBox("Type length in numbers")
    
    Sheet1.Activate
    
    Range("A2").End(xlDown).Offset(1, 0).Select
    
    ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
    ActiveCell.Offset(0, 1).Select
    
    ActiveCell.Value = NewFilmName
    
    MsgBox NewFilmName & " was added to the list"
    
    ActiveCell.Offset(0, 1).Select
    
    ActiveCell.Value = NewFilmDate
    
    ActiveCell.Offset(0, 1).Select
     
    ActiveCell.Value = NewFilmLength
    
    ActiveCell.End(xlToLeft).Offset(1, 0).Select
    


End Sub

 

[Jonmo1]

Both codes work fine for me.
Can you define "isn't working" ?
In what way does it not work?
Do you get an error? What error? On Which line ?
Does it not do what you're expecting it to do? What DOES it do? How is that different from what you expected?

All that said, You're probably better off without all the selecting, it's not necessary to Select or Activate Sheets/Ranges to manipulate them.

Try this

Option Explicit
Sub AddNewFilm()
Dim NewFilmName As String, NewFilmDate As Date, NewFilmLength As Integer
Dim NewCell As Range
NewFilmName = InputBox("Type a new film name")
NewFilmDate = InputBox("Type the date")
NewFilmLength = InputBox("Type length in numbers")
Set NewCell = Sheet1.Range("A2").End(xlDown).Offset(1, 0)
With NewCell
    .Value = .Offset(-1, 0).Value + 1
    .Offset(0, 1).Value = NewFilmName
    .Offset(0, 2).Value = NewFilmDate
    .Offset(0, 3).Value = NewFilmLength
End With
MsgBox NewFilmName & " was added to the list"
End Sub

 

[Fluff]

The only thing that is wrong with your 1st code is this line

Sheet1.Activate Range("A2").End(xlDown).Offset(1, 0).Select

which should be

Sheet1.Activate 
Range("A2").End(xlDown).Offset(1, 0).Select

Not sure if that's in your original code, or just something went wrong when you copied it to the board.