Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 an tried to use it and I got a runtime error '13' on the line beginnin mynum= I have attached the code that is giving the error, and another on which is identical. The second one works without any problem. Wh 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 ------------------- -- tanyhar ----------------------------------------------------------------------- tanyhart's Profile: http://www.excelforum.com/member.php...fo&userid=3514 View this thread: http://www.excelforum.com/showthread.php?threadid=57082 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro no longer working - Don Guillett
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 -------------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro no longer working - Don Guillett
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro no longer working - Don Guillett
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 -------------------- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro no longer working - Don Guillett
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 -------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF no longer working | Excel Discussion (Misc queries) | |||
SUMIF no longer working | Excel Worksheet Functions | |||
hyperlinks no longer working | Excel Discussion (Misc queries) | |||
Formulas no longer working | Excel Worksheet Functions | |||
Help: Excel 4 macro suddenly no longer working | Excel Programming |