Macro no longer working - Don Guillett

Don Guillett originally helped with the following macro to insert lines. It worked perfectly...until today. I went in to the spreadsheet and tried to use it and I got a runtime error '13' on the line beginning mynum=

I have attached the code that is giving the error, and another one which is identical. The second one works without any problem. Why would the first one not work anymore, when it once had?

Sub addtasks()
Application.DisplayAlerts = False
myrow = Cells.Find("Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1

With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.insert Shift:=xlDown
End With

Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True

End Sub

Sub addtask()
Application.DisplayAlerts = False
myrow = Cells.Find("Total Central Maintenance Shops Estimate").Row - 12
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1

With Range(Cells(myrow, 2), Cells(myrow + 11, 2))
.EntireRow.Copy
.EntireRow.insert Shift:=xlDown
End With

Application.CutCopyMode = False
Cells(myrow + 12, 2) = "Task#" & mynum
Application.DisplayAlerts = True

End Sub

sounds like the data you are processing is causing the error.

do you have any error values in that data?

--
Regards,
Tom Ogilvy


"tanyhart" wrote:

>
> Don Guillett originally helped with the following macro to insert lines.
> It worked perfectly...until today. I went in to the spreadsheet and
> tried to use it and I got a runtime error '13' on the line beginning
> mynum=
>
> I have attached the code that is giving the error, and another one
> which is identical. The second one works without any problem. Why
> would the first one not work anymore, when it once had?
>
>
> Code:
> --------------------
>
> Sub addtasks()
> Application.DisplayAlerts = False
> myrow = Cells.Find("Total P&C Estimate").Row - 3
> mycell = Cells(myrow, 2)
> mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
>
> With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
> .EntireRow.Copy
> .EntireRow.insert Shift:=xlDown
> End With
>
> Application.CutCopyMode = False
> Cells(myrow + 3, 2) = "Task#" & mynum
> Application.DisplayAlerts = True
>
> End Sub
>
> --------------------
>
>
>
> Code:
> --------------------
>
> Sub addtask()
> Application.DisplayAlerts = False
> myrow = Cells.Find("Total Central Maintenance Shops Estimate").Row - 12
> mycell = Cells(myrow, 2)
> mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
>
> With Range(Cells(myrow, 2), Cells(myrow + 11, 2))
> .EntireRow.Copy
> .EntireRow.insert Shift:=xlDown
> End With
>
> Application.CutCopyMode = False
> Cells(myrow + 12, 2) = "Task#" & mynum
> Application.DisplayAlerts = True
>
> End Sub
>
> --------------------
>
>
> --
> tanyhart
> ------------------------------------------------------------------------
> tanyhart's Profile: http://www.excelforum.com/member.php...o&userid=35148
> View this thread: http://www.excelforum.com/showthread...hreadid=570827
>
>

Hi tanyhart,

> myrow = Cells.Find("Total P&C Estimate").Row - 3

This line of code will fail if the text "Total P&C Estimate" is not found on
the active worksheet. What do you want to happen if that text is not found?
Or is it possible that the code is running when a different sheet is active?

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

tanyhart wrote:
> Don Guillett originally helped with the following macro to insert
> lines. It worked perfectly...until today. I went in to the
> spreadsheet and tried to use it and I got a runtime error '13' on the
> line beginning mynum=
>
> I have attached the code that is giving the error, and another one
> which is identical. The second one works without any problem. Why
> would the first one not work anymore, when it once had?
>
>
> Code:
> --------------------
>
> Sub addtasks()
> Application.DisplayAlerts = False
> myrow = Cells.Find("Total P&C Estimate").Row - 3
> mycell = Cells(myrow, 2)
> mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
>
> With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
> .EntireRow.Copy
> .EntireRow.insert Shift:=xlDown
> End With
>
> Application.CutCopyMode = False
> Cells(myrow + 3, 2) = "Task#" & mynum
> Application.DisplayAlerts = True
>
> End Sub
>
> --------------------
>
>
>
> Code:
> --------------------
>
> Sub addtask()
> Application.DisplayAlerts = False
> myrow = Cells.Find("Total Central Maintenance Shops Estimate").Row -
> 12 mycell = Cells(myrow, 2)
> mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
>
> With Range(Cells(myrow, 2), Cells(myrow + 11, 2))
> .EntireRow.Copy
> .EntireRow.insert Shift:=xlDown
> End With
>
> Application.CutCopyMode = False
> Cells(myrow + 12, 2) = "Task#" & mynum
> Application.DisplayAlerts = True
>
> End Sub
>
> --------------------

Sorry - misread your question and looked at the wrong line. Please
disregard.

Jake Marx wrote:
> Hi tanyhart,
>
>> myrow = Cells.Find("Total P&C Estimate").Row - 3
>
> This line of code will fail if the text "Total P&C Estimate" is not
> found on the active worksheet. What do you want to happen if that
> text is not found? Or is it possible that the code is running when a
> different sheet is active?
>
> tanyhart wrote:
>> Don Guillett originally helped with the following macro to insert
>> lines. It worked perfectly...until today. I went in to the
>> spreadsheet and tried to use it and I got a runtime error '13' on the
>> line beginning mynum=
>>
>> I have attached the code that is giving the error, and another one
>> which is identical. The second one works without any problem. Why
>> would the first one not work anymore, when it once had?
>>
>>
>> Code:
>> --------------------
>>
>> Sub addtasks()
>> Application.DisplayAlerts = False
>> myrow = Cells.Find("Total P&C Estimate").Row - 3
>> mycell = Cells(myrow, 2)
>> mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
>>
>> With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
>> .EntireRow.Copy
>> .EntireRow.insert Shift:=xlDown
>> End With
>>
>> Application.CutCopyMode = False
>> Cells(myrow + 3, 2) = "Task#" & mynum
>> Application.DisplayAlerts = True
>>
>> End Sub
>>
>> --------------------
>>
>>
>>
>> Code:
>> --------------------
>>
>> Sub addtask()
>> Application.DisplayAlerts = False
>> myrow = Cells.Find("Total Central Maintenance Shops Estimate").Row -
>> 12 mycell = Cells(myrow, 2)
>> mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
>>
>> With Range(Cells(myrow, 2), Cells(myrow + 11, 2))
>> .EntireRow.Copy
>> .EntireRow.insert Shift:=xlDown
>> End With
>>
>> Application.CutCopyMode = False
>> Cells(myrow + 12, 2) = "Task#" & mynum
>> Application.DisplayAlerts = True
>>
>> End Sub
>>
>> --------------------

Just an added thought for the benefit of the OP.

that's true, but I wouldn't see that causing a type mismatch error (error
13) two lines later.

--
Regards,
Tom Ogilvy


"Jake Marx" wrote:

> Hi tanyhart,
>
> > myrow = Cells.Find("Total P&C Estimate").Row - 3
>
> This line of code will fail if the text "Total P&C Estimate" is not found on
> the active worksheet. What do you want to happen if that text is not found?
> Or is it possible that the code is running when a different sheet is active?
>
> --
> Regards,
>
> Jake Marx
> www.longhead.com
>
>
> [please keep replies in the newsgroup - email address unmonitored]
>
> tanyhart wrote:
> > Don Guillett originally helped with the following macro to insert
> > lines. It worked perfectly...until today. I went in to the
> > spreadsheet and tried to use it and I got a runtime error '13' on the
> > line beginning mynum=
> >
> > I have attached the code that is giving the error, and another one
> > which is identical. The second one works without any problem. Why
> > would the first one not work anymore, when it once had?
> >
> >
> > Code:
> > --------------------
> >
> > Sub addtasks()
> > Application.DisplayAlerts = False
> > myrow = Cells.Find("Total P&C Estimate").Row - 3
> > mycell = Cells(myrow, 2)
> > mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
> >
> > With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
> > .EntireRow.Copy
> > .EntireRow.insert Shift:=xlDown
> > End With
> >
> > Application.CutCopyMode = False
> > Cells(myrow + 3, 2) = "Task#" & mynum
> > Application.DisplayAlerts = True
> >
> > End Sub
> >
> > --------------------
> >
> >
> >
> > Code:
> > --------------------
> >
> > Sub addtask()
> > Application.DisplayAlerts = False
> > myrow = Cells.Find("Total Central Maintenance Shops Estimate").Row -
> > 12 mycell = Cells(myrow, 2)
> > mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
> >
> > With Range(Cells(myrow, 2), Cells(myrow + 11, 2))
> > .EntireRow.Copy
> > .EntireRow.insert Shift:=xlDown
> > End With
> >
> > Application.CutCopyMode = False
> > Cells(myrow + 12, 2) = "Task#" & mynum
> > Application.DisplayAlerts = True
> >
> > End Sub
> >
> > --------------------
>
>
>