Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
on error
Hi
I have a macro with a selection.find when the code that it is trying to find is not in selection its trapped by on error goto myerr this is in a for each cell in range and next part of macro .it works first time error happens but not 2nd? the appropiate part of code is below any ideas? Thanks Tina col = Range("myCodes").Column firstRow = Range("MyCodes").Rows(1).Row lastRow = Range("Mycodes").Rows(Range("MyCodes").Rows.Count) .Row For i = lastRow To firstRow Step -1 MYCell = Cells(i, col) myvalue = Cells(i, col).Offset(0, 6).Value - 1 If myvalue 0 Then Cells(i, col).Offset(1, 0).Resize(myvalue).EntireRow.Insert Cells(i, col).EntireRow.Copy Cells(i, 1).Resize(myvalue + 1).Select ActiveSheet.Paste Application.CutCopyMode = False Windows("COMPONENTS.xls").Activate Columns("A:A").Select Selection.Find(What:=MYCell, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate ActiveCell.Offset(0, 1).Resize(myvalue + 1, 4).Select Selection.Copy Windows("PLANNEDJOBS.xls").Activate Cells(i, 12).Select ActiveSheet.Paste Else Windows("COMPONENTS.xls").Activate On Error GoTo MYERR Columns("A:A").Select Selection.Find(What:=MYCell, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate ActiveCell.Range("b1:e1").Copy Windows("PLANNEDJOBS.xls").Activate Cells(i, 12).Select ActiveSheet.Paste End If MYERR: Windows("PLANNEDJOBS.xls").Activate Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
on error
Dim rng as Range
col = Range("myCodes").Column firstRow = Range("MyCodes").Rows(1).Row lastRow = Range("Mycodes").Rows(Range("MyCodes").Rows.Count) .Row For i = lastRow To firstRow Step -1 MYCell = Cells(i, col) myvalue = Cells(i, col).Offset(0, 6).Value - 1 If myvalue 0 Then Cells(i, col).Offset(1, 0).Resize(myvalue).EntireRow.Insert Cells(i, col).EntireRow.Copy Cells(i, 1).Resize(myvalue + 1).Select ActiveSheet.Paste Application.CutCopyMode = False Windows("COMPONENTS.xls").Activate set rng = Columns("A:A").Find(What:=MYCell, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) if not rng is nothing then rng.Select ActiveCell.Offset(0, 1).Resize(myvalue + 1, 4).Select Selection.Copy Windows("PLANNEDJOBS.xls").Activate Cells(i, 12).Select ActiveSheet.Paste Else Windows("COMPONENTS.xls").Activate Set rng = Columns("A:A").Find(What:=MYCell, _ After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) if not rng is nothing then rng.select ActiveCell.Range("b1:e1").Copy Windows("PLANNEDJOBS.xls").Activate Cells(i, 12).Select ActiveSheet.Paste End If End If Windows("PLANNEDJOBS.xls").Activate Next -- Regards, Tom Ogilvy "tina" wrote in message ... Hi I have a macro with a selection.find when the code that it is trying to find is not in selection its trapped by on error goto myerr this is in a for each cell in range and next part of macro .it works first time error happens but not 2nd? the appropiate part of code is below any ideas? Thanks Tina col = Range("myCodes").Column firstRow = Range("MyCodes").Rows(1).Row lastRow = Range("Mycodes").Rows(Range("MyCodes").Rows.Count) .Row For i = lastRow To firstRow Step -1 MYCell = Cells(i, col) myvalue = Cells(i, col).Offset(0, 6).Value - 1 If myvalue 0 Then Cells(i, col).Offset(1, 0).Resize(myvalue).EntireRow.Insert Cells(i, col).EntireRow.Copy Cells(i, 1).Resize(myvalue + 1).Select ActiveSheet.Paste Application.CutCopyMode = False Windows("COMPONENTS.xls").Activate Columns("A:A").Select Selection.Find(What:=MYCell, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate ActiveCell.Offset(0, 1).Resize(myvalue + 1, 4).Select Selection.Copy Windows("PLANNEDJOBS.xls").Activate Cells(i, 12).Select ActiveSheet.Paste Else Windows("COMPONENTS.xls").Activate On Error GoTo MYERR Columns("A:A").Select Selection.Find(What:=MYCell, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate ActiveCell.Range("b1:e1").Copy Windows("PLANNEDJOBS.xls").Activate Cells(i, 12).Select ActiveSheet.Paste End If MYERR: Windows("PLANNEDJOBS.xls").Activate Next |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Drillthrough MDX ERROR(Error.Number:-21467259) ?(urgent question) | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming | |||
Error 50290: Error writing to Worksheet while using an ActiveX Control | Excel Programming |