Page 1 of 5 123 ... LastLast
Results 1 to 10 of 48

Excel Macro not working with excel 2010

This is a discussion on Excel Macro not working with excel 2010 within the Excel Questions forums, part of the Question Forums category; Hi all, I have a excel macro written in excel 2003. This macro is run from my vb .net application. ...

  1. #1
    Board Regular
    Join Date
    Mar 2009
    Posts
    142

    Default Excel Macro not working with excel 2010

    Hi all,

    I have a excel macro written in excel 2003.
    This macro is run from my vb.net application.
    The macro runs successfully on excel 2003 & 2007 but with excel 2010, I get the below error message:
    Execution error:1004
    No corresponding cells.

    Not sure why this is happening...
    Any suggestion, advic,solution will be helpful.

    Thanks in advance.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,071

    Default Re: Excel Macro not working with excel 2010

    What line of code is causing that error?
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Mar 2009
    Posts
    142

    Default Re: Excel Macro not working with excel 2010

    Thanks for the reply.
    Currently I have 2007 on my PC.
    I will check on another pc that has 2010 for the exact line of code that produces the error.

    The below is the code that produces the error:
    Code:
    Sub DeleteDuplicateOrangeX()
    'Compare columns G,L,M & W for duplicated rows, delete the ones that
    'are duplicates AND have an orange color in column X
    Dim LR As Long, Rng As Range, Cell As Range
    Application.ScreenUpdating = False
    LR = Range("A" & Rows.Count).End(xlUp).Row
    'Create key columns to evaluate columns of data
        Range("CC5:CC" & LR).FormulaR1C1 = "=RC7&""-""&RC12&""-""&RC13&""-""&RC23"
        Range("CD5:CE" & LR).FormulaR1C1 = "=COUNTIF(C81,RC81)>1"
        Range("CC5:CE" & LR).Value = Range("CC5:CE" & LR).Value
        Range("CD3") = "key"
     
    'Autofilter duplicated rows
        Range("CD3:CE" & LR).AutoFilter Field:=1, Criteria1:=True
        Set Rng = Range("CD5:CE" & LR).SpecialCells(xlCellTypeVisible)
    'Flag duplicates with orange "X" columns
        For Each Cell In Rng
            If Cells(Cell.Row, "X").Interior.ColorIndex = 44 Then Cell = 1
        Next Cell
     
    'Refilter by flagged rows and delete
        Range("CD3:CE" & LR).AutoFilter Field:=1, Criteria1:=1
        If Range("CE" & Rows.Count).End(xlUp).Row > 4 Then _
        Range("A5:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp
    'Cleanup
    ActiveSheet.AutoFilterMode = False
    Range("CC:CE").ClearContents
    Rows(4).Hidden = True
    Application.ScreenUpdating = True
    End Sub
    I remember the error was produced on the range line.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,071

    Default Re: Excel Macro not working with excel 2010

    You will get that error in any version of Excel if there are no cells of the type specified in the SpecialCells method.
    Microsoft MVP - Excel

  5. #5
    Board Regular
    Join Date
    Mar 2009
    Posts
    142

    Default Re: Excel Macro not working with excel 2010

    Then how to solve this issue?
    I'm not clear about your statement.

    Thanks for the reply.

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,071

    Default Re: Excel Macro not working with excel 2010

    You will have to trap the error.
    Microsoft MVP - Excel

  7. #7
    Board Regular
    Join Date
    Mar 2009
    Posts
    142

    Default Re: Excel Macro not working with excel 2010

    I used the on error to handle the error.
    This eliminated the error, but I found that the output was wrong.
    The code has to remove lines that have duplicate lines with values in columns 7,12,13, and 23 along with orange color in the cell of a row.
    But it remove the duplicates along with the original(or one row that has to be kept in the excel)

    Code:
    Code:
    Sub DeleteDuplicateOrangeX()
    'Compare columns G,L,M & W for duplicated rows, delete the ones that
    'are duplicates AND have an orange color in column X
    Dim LR As Long, Rng As Range, Cell As Range
    Application.ScreenUpdating = False
    LR = Range("A" & Rows.Count).End(xlUp).Row
    'Create key columns to evaluate columns of data
        Range("CC5:CC" & LR).FormulaR1C1 = "=RC7&""-""&RC12&""-""&RC13&""-""&RC23"
        Range("CD5:CE" & LR).FormulaR1C1 = "=COUNTIF(C81,RC81)>1"
        Range("CC5:CE" & LR).Value = Range("CC5:CE" & LR).Value
        Range("CD3") = "key"
        
    'Autofilter duplicated rows
        Range("CD3:CE" & LR).AutoFilter Field:=1, Criteria1:=True
        'Set Rng = Range("CD5:CE" & LR).SpecialCells(xlCellTypeVisible)
        On Error Resume Next
        Set Rng = Range("CD5:CE" & LR).SpecialCells(xlCellTypeVisible)
       Exit Sub
    'Flag duplicates with orange "X" columns
        For Each Cell In Rng
            If Cells(Cell.Row, "X").Interior.ColorIndex = 44 Then Cell = 1
        Next Cell
        
    'Refilter by flagged rows and delete
        Range("CD3:CE" & LR).AutoFilter Field:=1, Criteria1:=1
        If Range("CE" & Rows.Count).End(xlUp).Row > 4 Then _
            Range("A5:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp
    'Cleanup
    ActiveSheet.AutoFilterMode = False
    Range("CC:CE").ClearContents
    Rows(4).Hidden = True
    Application.ScreenUpdating = True
    End Sub

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,071

    Default Re: Excel Macro not working with excel 2010

    What's that Exit Sub doing in the middle of your procedure? Currently it appears that nothing is being deleted.
    Microsoft MVP - Excel

  9. #9
    Board Regular
    Join Date
    Mar 2009
    Posts
    142

    Default Re: Excel Macro not working with excel 2010

    If an error is encountered in the below line,
    Code:
    Set Rng = Range("CD5:CE" & LR).SpecialCells(xlCellTypeVisible)
    On error resume next will be followed by the exit sub line.I want to exit the sub, without checking the other lines of code.

    If I'm wrong, then how to proceed?
    Thanks for any help.

  10. #10
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,071

    Default Re: Excel Macro not working with excel 2010

    Like this?

    Code:
    On Error Resume Next
    Set Rng = Range("CD5:CE" & LR).SpecialCells(xlCellTypeVisible)
    If Err <> 0 Then Exit Sub
    On Error GoTo 0
    Microsoft MVP - Excel

Page 1 of 5 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com