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

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

Jim Thomlinson


"Novice" wrote:

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".

"Jim Thomlinson" wrote:

What is the value of j when the error is generated. My guess would be that
the line:

j = DataRowNo2

is never executed so j is never initialized, meaning that it will have a
default value of 0. Since row 0 does not exist the error is generated.
--
HTH...

Jim Thomlinson


"Novice" wrote:

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

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


"Novice" wrote:

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

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.

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

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".

"Jim Thomlinson" wrote:

What is the value of j when the error is generated. My guess would be that
the line:

j = DataRowNo2

is never executed so j is never initialized, meaning that it will have a
default value of 0. Since row 0 does not exist the error is generated.
--
HTH...

Jim Thomlinson


"Novice" wrote:

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

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

"sebastienm" wrote:

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


"Novice" wrote:

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

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...
--
HTH...

Jim Thomlinson


"Novice" wrote:

I did what you suggested and the code did not stop. Any solution to the
problem? Thanks.

"Jim Thomlinson" wrote:

It does not appear as if j has ever been initialized. Place a break point on
the line

j = DataRowNo2

and rerun the code. If the code execution does not stop on that line then
that line never executes and you have found (not fixed but found) your
problem.
--
HTH...

Jim Thomlinson


"Novice" wrote:

it says value of j <out of context?? :(

I have 0 in cell G2 on sheet "Comments", and 1 in cell G3, shouldn't j=3? I
am confused.

thanks

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Novice" wrote:

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".

"Jim Thomlinson" wrote:

What is the value of j when the error is generated. My guess would be that
the line:

j = DataRowNo2

is never executed so j is never initialized, meaning that it will have a
default value of 0. Since row 0 does not exist the error is generated.
--
HTH...

Jim Thomlinson


"Novice" wrote:

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

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.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Novice" wrote:

You are right, j=0. but I need to use j to decide which row in "Comments"
sheet to start pasting. DataRow2 is between 2 and 30, and j=DataRow2, why
j=0 happened? I am confused.

thanks

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

--
Regards,
Tom Ogilvy




"Jim Thomlinson" wrote in
message ...
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...
--
HTH...

Jim Thomlinson


"Novice" wrote:

I did what you suggested and the code did not stop. Any solution to the
problem? Thanks.

"Jim Thomlinson" wrote:

It does not appear as if j has ever been initialized. Place a break
point on
the line

j = DataRowNo2

and rerun the code. If the code execution does not stop on that line
then
that line never executes and you have found (not fixed but found) your
problem.
--
HTH...

Jim Thomlinson


"Novice" wrote:

it says value of j <out of context?? :(

I have 0 in cell G2 on sheet "Comments", and 1 in cell G3, shouldn't
j=3? I
am confused.

thanks

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Novice" wrote:

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".

"Jim Thomlinson" wrote:

What is the value of j when the error is generated. My guess
would be that
the line:

j = DataRowNo2

is never executed so j is never initialized, meaning that it
will have a
default value of 0. Since row 0 does not exist the error is
generated.
--
HTH...

Jim Thomlinson


"Novice" wrote:

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

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

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


"Novice" wrote:

You are right, j=0. but I need to use j to decide which row in "Comments"
sheet to start pasting. DataRow2 is between 2 and 30, and j=DataRow2, why
j=0 happened? I am confused.

thanks

"sebastienm" wrote:

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.

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

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.
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy




"Jim Thomlinson" wrote in
message ...
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...
--
HTH...

Jim Thomlinson


"Novice" wrote:

I did what you suggested and the code did not stop. Any solution to the
problem? Thanks.

"Jim Thomlinson" wrote:

It does not appear as if j has ever been initialized. Place a break
point on
the line

j = DataRowNo2

and rerun the code. If the code execution does not stop on that line
then
that line never executes and you have found (not fixed but found) your
problem.
--
HTH...

Jim Thomlinson


"Novice" wrote:

it says value of j <out of context?? :(

I have 0 in cell G2 on sheet "Comments", and 1 in cell G3, shouldn't
j=3? I
am confused.

thanks

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Novice" wrote:

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".

"Jim Thomlinson" wrote:

What is the value of j when the error is generated. My guess
would be that
the line:

j = DataRowNo2

is never executed so j is never initialized, meaning that it
will have a
default value of 0. Since row 0 does not exist the error is
generated.
--
HTH...

Jim Thomlinson


"Novice" wrote:

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

Thanks a million!

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy




"Jim Thomlinson" wrote in
message ...
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...
--
HTH...

Jim Thomlinson


"Novice" wrote:

I did what you suggested and the code did not stop. Any solution to the
problem? Thanks.

"Jim Thomlinson" wrote:

It does not appear as if j has ever been initialized. Place a break
point on
the line

j = DataRowNo2

and rerun the code. If the code execution does not stop on that line
then
that line never executes and you have found (not fixed but found) your
problem.
--
HTH...

Jim Thomlinson


"Novice" wrote:

it says value of j <out of context?? :(

I have 0 in cell G2 on sheet "Comments", and 1 in cell G3, shouldn't
j=3? I
am confused.

thanks

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Novice" wrote:

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".

"Jim Thomlinson" wrote:

What is the value of j when the error is generated. My guess
would be that
the line:

j = DataRowNo2

is never executed so j is never initialized, meaning that it
will have a
default value of 0. Since row 0 does not exist the error is
generated.
--
HTH...

Jim Thomlinson


"Novice" wrote:

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