Runtime error 1004- application defined or object defined error

[Novice]

Hi, Thanks in advance. I am really a novice for VB. please don't laugh at my
silly codes. I am trying to copy info on "DataEntry" sheet to sheets "Data"
and "Comments". It works well pasting to "Data" but I got error msg like
"Runtime error 1004- application defined or object defined error" when
pasting to "Comments". Please see what's wrong with my code as below:

Private Sub Submit_Click()
‘Data Sheet
For DataRowNo = 2 To 100
If Worksheets("Data").Cells(DataRowNo, 17) = 1 Then
i = DataRowNo
Else
DataRowNo = DataRowNo + 1
End If
Next

Worksheets("Data").Cells(i, 1) = Worksheets("DataEntry").Cells(5, 3)
Worksheets("Data").Cells(i, 2) = Worksheets("DataEntry").Cells(6, 3)

‘Comment sheet
For DataRowNo2 = 2 To 30 '
If Worksheets("Comments").Cells(DataRowNo2, 7) = 1 Then
j = DataRowNo2
Else
DataRowNo2 = DataRowNo2 + 1
End If
Next
‘Error line (run time error 1004)
Worksheets("Comments").Cells(j, 3) = Worksheets("DataEntry").Cells(29, 21)
'Worksheets("Comments").Cells(j, 3) = Worksheets("DataEntry").Cells(37, 21)

End sub

 

[Jim Thomlinson]

So what it is the value of j. Place a break point on that line (F9 key) and
add a watch (highlight the variable and right click -> Add Watch).

 

[sebastienm]

Hi,
it could come from the fact that, in your DataRowNo2 loop, j is assigned
only if '1' is found. So , if there is not a single '1', j stays 0 (zero).
Therefore , line
Worksheets("Comments").Cells(j, 3)
with j=0, means
....Cells(0, 3) ---> it starts at cells(1,1)... no zero
which does not exist therefore the error.

The same thing could happen in the loop for the Data sheet. It just depends
whether i, or j, is assigned or stays zero.

A quick fix...
Wrap the potential error lines in an if statement:
If j<>0 then
Worksheets("Comments").Cells(j, 3) = Worksheets("DataEntry").Cells(29,
21)
Worksheets("Comments").Cells(j, 3) = Worksheets("DataEntry").Cells(37,
21)
Else
'something else here
End if

and same for the loop for Data (with i)

Regards,
Sébastien
<http://www.ondemandanalysis.com>

 

[sebastienm]

To make sure of that, send a msgbox right before the error:
MsgBox "j= " & j

Worksheets("Comments").Cells(j, 3) = Worksheets("DataEntry").Cells(29, 21)
'Worksheets("Comments").Cells(j, 3) = Worksheets("DataEntry").Cells(37, 21)

Also you can run in debug mode. Put a breakpoint at the 'For DataRowNo2...'
line, then press F8 to run line-by-line and evaluate your variables and
expressions.

 

[Novice]

DataRowNo2 is between 2 and 30, and j should not be 0. The loop is to make
sure to "append" record in sheet "Comment", coz it might already have data
there. And i works fine in sheet "Data".

 

[Novice]

I put a fomula on sheet "Comments" column G: G2=if(isblank(A2), G2=G1+1,G1).
and there is a '1' there.

 

[Jim Thomlinson]

Somewhere on your Data sheet between rows 2 and 100 in column 7 you have the
number 1, so that top portion of code works. In the comments sheet between
rows 2 and 30 of column 7 it is not finding the number 1. A couple of things
come to mind. Either that number just does not exist in that range or the
number is not actually a number, but rather it is text (The number 1 and text
digit 1 are two completely different things). First of make sure the NUMBER 1
exists in the range...

 

[sebastienm]

Here are a few general things first:

1. A good practice is to declare all variables, else variables are Variant
by default. Declaring a variable means you tell in advance to vba that you
are going to use a variable called 'j' and that it is going to be an Integer
(not a string not a real number).... and a few more things not to worry about
at this point.
This forces values to be within a certain range. Eg: if j is an integer,
it can't be the string "hello" else an error occurs which puts you on the
track for debugging.
Now, how to handle declaration. Declare variable at the top of a Sub or a
Function with the syntax:
Dim <variable_name> As <Varible_type>
Several data type exists: Integer, Long (similar to integer but can take
larger values), Single (real number), Double (like single but can take larger
values), String, ... *** >>> See online-help and search in the Answer Wizard
for "Data Type SUmmary". It will give you a table of basic data types and
which values they can take.
In your sub you would do:
Private Sub Submit_Click()
Dim i as Long, j as Long
Dim DataRowNo as Long,DataRowNo2 as Long
...

2. To force you to explicitely declare variables (good practice), in the vba
editor, go to menu Tools > Options , tab Editor, check Require Variable
Declaration (and at the same time you may want to uncheck the 'Auto Syntax
Check' which still tells you a line is wrong but by changing its color to red
instead of poping up the abnoxious message box)
------------------------------
3. Now back to your example, let's try to log time we loop the values of
interest:
After the line:
For DataRowNo2 = 2 To 30
Add the line of code:
Debug.Print Worksheets("Comments").Cells(DataRowNo2, 7).Address, _
Worksheets("Comments").Cells(DataRowNo2, 7).Value, _
Worksheets("Comments").Cells(DataRowNo2, 7) = 1

Debug.Print statement sends the expressions
(Worksheets("Comments").Cells(DataRowNo2, 7).Address) and
(Worksheets("Comments").Cells(DataRowNo2, 7).Value, and the result true/false
of Worksheets("Comments").Cells(DataRowNo2, 7) = 1) , which are in facte the
cell address and the cell value of what we are looking for, to the Immediate
Window during execution.
If the Immediate Window is not visible in your VBA Editor environment, you
can display it through the menu View > Immediate Window.

Now run the macro again and look at the result in the immediate window. Do
you see the cell that contains 1? And doe sthe comparison shows a True or a
False?

Not sure i was very clear. I hope i was.

 

[Tom Ogilvy]

Try running your code like this and see if you get better results:

Private Sub Submit_Click()
'Data Sheet
For DataRowNo = 2 To 100
If Worksheets("Data").Cells(DataRowNo, 17) = 1 Then
i = DataRowNo
End If
Next

Worksheets("Data").Cells(i, 1) = Worksheets("DataEntry").Cells(5, 3)
Worksheets("Data").Cells(i, 2) = Worksheets("DataEntry").Cells(6, 3)

'Comment sheet
For DataRowNo2 = 2 To 30 '
If Worksheets("Comments").Cells(DataRowNo2, 7) = 1 Then
j = DataRowNo2
End If
Next
'Error line (run time error 1004)
Worksheets("Comments").Cells(j, 3) = Worksheets("DataEntry").Cells(29, 21)
'Worksheets("Comments").Cells(j, 3) = Worksheets("DataEntry").Cells(37, 21)

End sub

You shouldn't increment you loop counter interior to the loop. I believe
you are doing the equivalent

Sub tester1()
For i = 1 To 10
Debug.Print i
i = i + 1
Next
End Sub

when i do this I get

1
3
5
7
9

so when your condition is not met, you are skipping over some of your data

 

[sebastienm]

OK i got it. And Sorry i didn't catch that earlier

A For-Next loop adds 1 to the loop-variable (here DataRowNo2)
automatically, there is no need of doing DataRowNo2 = DataRowNo2 + 1
Even worse, the fact that you add DataRowNo2=DataRowNo2+1 jump every other
number (2,4,6,8...) because within 1 loop you add 1 through code and the
For-Next adds 1 automatically therefore at each loop the value is increased
by 2 ie every other cell is checks , not all of them.
The same thing happens with DataRowNo, you're just lucky the '1' appears on
an even row number.
So you would replace the code by
For DataRowNo2 = 2 To 30 '
If Worksheets("Comments").Cells(DataRowNo2, 7) = 1 Then j = DataRowNo2
Next

 

[Jim Thomlinson]

Thanks Tom. Yours is by far the most likely solution. It must be monday
because I missed that the loop was being incremented in the else statement.

 

[Novice]

Thanks a million!

Try running your code like this and see if you get better results:

Private Sub Submit_Click()
'Data Sheet
For DataRowNo = 2 To 100
If Worksheets("Data").Cells(DataRowNo, 17) = 1 Then
i = DataRowNo
End If
Next

Worksheets("Data").Cells(i, 1) = Worksheets("DataEntry").Cells(5, 3)
Worksheets("Data").Cells(i, 2) = Worksheets("DataEntry").Cells(6, 3)

'Comment sheet
For DataRowNo2 = 2 To 30 '
If Worksheets("Comments").Cells(DataRowNo2, 7) = 1 Then
j = DataRowNo2
End If
Next
'Error line (run time error 1004)
Worksheets("Comments").Cells(j, 3) = Worksheets("DataEntry").Cells(29, 21)
'Worksheets("Comments").Cells(j, 3) = Worksheets("DataEntry").Cells(37, 21)

End sub

You shouldn't increment you loop counter interior to the loop. I believe
you are doing the equivalent

Sub tester1()
For i = 1 To 10
Debug.Print i
i = i + 1
Next
End Sub

when i do this I get

1
3
5
7
9

so when your condition is not met, you are skipping over some of your data