Error:- Run-time error '1004'

0
votes
Sub DataInputBox()

    Dim Id As Integer
    Dim Name As String
    Dim gender As String

    Id = InputBox("Enter in your id")
    Name = InputBox("Enter in your Name")
    gender = InputBox("Enter in your gender")

    Sheets(2).Range("a1").End(xlDown).Offset(1, 0).Value = Id
    Sheets(2).Range("a1").End(xlDown).Offset(1, 1).Value = Name
    Sheets(2).Range("a1").End(xlDown).Offset(1, 2).Value = gender

End Sub

I'm getting run-time error for above code while execute in excel.

Error:- Run-time error '1004' Application-defined or object-defined error

4
vbaexcel
What does msgbox(Sheets(2).Range("a1").End(xlDown).Address) tell you? - KekuSemau
it will give last cell address in column A. - sai krishna
Do you have anything in the second worksheet's column A? You cannot offset 1 row down if xlDown has already taken you to the bottom of the worksheet. - user4039065
Right, that's what I meant, if column A is empty and also if there is only one value, you jump the end of the sheet and cannot offset further. - KekuSemau
ok, thank you. But how i can put "ID" value in last cell (Column A) - sai krishna

4 Answers

3
votes

Try it as,

Sheets(2).Range("a" & rows.count).End(xlUP).Offset(1, 0).Value = Id
Sheets(2).Range("a" & rows.count).End(xlUP).Offset(0, 1).Value = Name
Sheets(2).Range("a" & rows.count).End(xlUP).Offset(0, 2).Value = gender
1
votes

You need some material to be present in column A of Sheet2. Starting with:

enter image description here

I ran your code (ran without error) and got:

enter image description here

Just be sure you have reasonable data in the proper sheet.

0
votes

Try the below

Sheets(2).Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Value = ID
Sheets(2).Range("B" & Range("A" & Rows.Count).End(xlUp).Row).Value = Name
Sheets(2).Range("C" & Range("A" & Rows.Count).End(xlUp).Row).Value = gender
0
votes

Just for fun a forth solution:

Sub DataInputBox()

  Dim Id As Long
  Dim xName As String
  Dim gender As String

  Id = InputBox("Enter in your id")
  xName = InputBox("Enter in your Name")
  gender = InputBox("Enter in your gender")

  Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 3).Value = Array(ID, xName, gender)


End Sub

However, better not declare Name as a variable or VBA may get messed up because as good as every object has it as a property. ;)