PDA

View Full Version : object or with block variable not set??



samuelimtech
01-15-2015, 09:55 AM
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
01-15-2015, 12:11 PM
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
01-16-2015, 01:56 AM
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
01-16-2015, 02:00 AM
On the contrary.

Bob Phillips
01-16-2015, 03:09 AM
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
01-16-2015, 09:18 AM
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
01-16-2015, 09:30 AM
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
01-19-2015, 01:25 AM
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...