object or with block variable not set??

[samuelimtech]

Hi all can anyone see an issue with the below code??

For Column = 12 To 18
For Row = 5 To 47
    On Error GoTo no1
    
    If Not Workbooks(Timesheet).Sheets("Timesheet").Cells(Row, Column).Comment.Text = "" Then
    Comment = Workbooks(Timesheet).Sheets("Timesheet").Cells(Row, Column).Comment.Text
    Workbooks(Temp).Sheets("Timesheet").Cells(Row, Column).AddComment.Text Comment
    
    End If
no1:
    Next
    Next

for some reason it goes through once but then on the second time fails.

FYI thi part of the code copies a range then copies any comments made in that range to a new workbook, if anyone knows of a simple way to copy values and comments that would be easier, thanks.

[Bob Phillips]

Should it perhaps be Workbooks("Timesheet.xlsx") and Workbooks("Temp.xlsx")?

BTW, it is a terrible idea to use variables with the same names as OM properties (Column, Row, Comment).

[samuelimtech]

Hi,

thanks for the reply, no Timesheet is a variable set to be Timesheet.xlsx.

LOL dont worry im not using variables with the same name as properties this is purely for putting my code on here, its simpler to understand.

[snb]

On the contrary.

[Bob Phillips]

Timesheet is a variable set to be Timesheet.xlsx.

Then it should be

Timesheet.Sheets("Timesheet").Cells(Row, Column).Comment.Text

not

Workbooks(Timesheet).Sheets("Timesheet").Cells(Row, Column).Comment.Text

and so on.

[SamT]

For Col = 12 To 18
For Rw = 5 To 47
    On Error GoTo LoopAgain
    
    Cmmnt =Timesheet.Sheets("Timesheet").Cells(Rw, Col).Comment.Text
    If Len(Cmmnt) > 0 Then _
    Workbooks("Temp.xlsx").Sheets("Timesheet").Cells(Rw, Col).AddComment.Text Cmmnt
    
    
LoopAgain:
    Next
    Next

dont worry im not using variables with the same name as properties this is purely for putting my code on here, its simpler to understand.

No it's not!

[SamT]

Set Src = Workbooks("Timesheet.xls").Sheets("Timesheet")
Set Dest = Workbooks("Temp").Sheets("Timesheet")

For Col = 12 To 18
For Rw = 5 To 47
    On Error GoTo LoopAgain
    
    Src .Cells(Rw, Col).Copy
    Dest.Cells(Rw, Col).PasteSpecial (xlPasteComments)
   
LoopAgain:
    Next
    Next

[Aflatoon]

FYI, the reason it goes through once and fails the second time is that your error handling is incorrect. It's actually raising an error on the first pass which is handled by your 'On Error Goto no1' line but because you don't have a Resume Statement (or other method to clear the exception) everything after that is effectively an error handler statement and any error in an error handler is unhandled.

However, as others have said, it's impossible to tell what's wrong with your code when what you post is not your code...