Error:- Run-time error '1004'

Question

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

What does `msgbox(Sheets(2).Range("a1").End(xlDown).Address)` tell you? — KekuSemau, Apr 30 '16 at 11:55
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. — , Apr 30 '16 at 12:03
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, Apr 30 '16 at 12:05
ok, thank you. But how i can put "ID" value in last cell (Column A) — sai krishna, Apr 30 '16 at 12:08
In sheet, i have only "Headers" and i need update value in last cell (Column A) — sai krishna, Apr 30 '16 at 12:09

score 3 · Answer 1 · answered Apr 30 '16 at 12:06

3

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

answered Apr 30 '16 at 12:06

3

Also see http://stackoverflow.com/a/11169920/6216216 why this is superior to using `End(xlDown)`. – Leviathan Apr 30 '16 at 12:08

score 1 · Accepted Answer · answered Apr 30 '16 at 12:05

1

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

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

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

answered Apr 30 '16 at 12:05

Gary's Student

95,722
10
59
99

score 0 · Answer 3 · answered Apr 30 '16 at 15:03

0

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

answered Apr 30 '16 at 15:03

Karthick Gunasekaran

2,697
1
15
25

score 0 · Answer 4 · answered Apr 30 '16 at 17:43

0

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. ;)

answered Apr 30 '16 at 17:43

Dirk Reichel

7,989
1
15
31

Could you please explain more about resize command & give simple examples – sai krishna Apr 30 '16 at 19:36
@saikrishna Pls check the [MSDN-help center](https://msdn.microsoft.com/en-us/library/office/ff193274.aspx). If you still do not get it, just tell me and I will try to explain it as good as possible ;) – Dirk Reichel Apr 30 '16 at 19:39
OK. `Range("C4:K99").Resize(22,15)` will be `C4:Q25`. The upper left cell (C4) will stay the same while the height and width of the new range is directly given at the resize part. You also can omit a parameter like `Range("C4:K99").Resize(,15)` to get the range `C$4:$Q$99`. – Dirk Reichel Apr 30 '16 at 20:07
I understood and i don't want to put exact values in resize command and i want to use columns.count function in below code. Could you please write... Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 3).Value = Array(ID, xName, gender) – sai krishna Apr 30 '16 at 20:24
For what reason would you use `columns.count` in that line? You are looking for the last cell in column A. `columns.count` would probably go for column XFD and why do you want to be there? Also being at the `Resize`-part then better use `.EntireRow` to get all columns ;) – Dirk Reichel Apr 30 '16 at 20:39

Error:- Run-time error '1004'

4 Answers4