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

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