Results 1 to 3 of 3

VBA evaluate code error

Threaded View

  1. #1
    Forum Contributor
    Join Date
    12-03-2009
    Location
    Providence RI
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    260

    VBA evaluate code error

    I am offically going to go crazy! LOL. I have some VB code that is in a seperate workbook that I run via a button placed on the active worksheet. Here is the relevant section of code that "sometimes" causes issues.


    The workbook is located on sharepoint but it would be open any time this macro is ran. I personally never have any issues at all with this macro, but others do. For some people it will work once but then it throws debug errors saying Object can not be found. The file open is named the exact same as indicated in the code. The rest of the code finds the workbook, so any idea why maybe the problem section "sometimes" throws a unable to locate object error?


    I probably have some duplicate code that isn't nessecary as I tend to go in and add little bits here and there. Portions of the code were helped to be put together with some forum members and I appredicate that. I probably have a few extra activate lines but for some reason even tho the With's would refer to a certain sheet it wouldnt actually do the tasks on that sheet unless I activated it.



    
    Worksheets("BO Ref List").Activate
    
        Set r = Workbooks("P-score and Q-score trends.xlsx").Worksheets("BO Ref List").Range("B:D")
    
            r.Value = Evaluate("IF(ISNUMBER(" & r.Address & ")," & r.Address & ",PROPER(" & r.Address & "))")
    
    Worksheets("FO Ref List").Activate
    
        Set r = Workbooks("P-score and Q-score trends.xlsx").Worksheets("FO Ref List").Range("B:D")
    
            r.Value = Evaluate("IF(ISNUMBER(" & r.Address & ")," & r.Address & ",PROPER(" & r.Address & "))")
    
    Windows("P-score and Q-score trends.xlsx").Activate
    
        With ActiveWorkbook.Names("BOAssociate")
    
            .Name = "BOAssociate"
    
            .RefersToR1C1 = _
    
            "=OFFSET('BO Ref List'!RC[-2],0,0,COUNTA('BO Ref List'!R2C2:R989C2),1)"
    
            .Comment = ""
    
        End With


    This is the complete code just in case there is something else causing the problem.



    
    Sub RefreshLists()
    
    '
    
    ' Macro to Refresh lists, will clear and fill new data.
    
    '
    
    '
    
    ' BO Section
    
    Workbooks("P-score and Q-score trends.xlsx").Worksheets("BO Ref List").Activate
    
        With Workbooks("P-score and Q-score trends.xlsx")
    
            .Worksheets("BO Ref List").Cells.ClearContents
    
            .Worksheets("BO Overall Data").Columns("G:H").Copy
    
            With .Worksheets("BO Ref List")
    
                .Range("A1").PasteSpecial
    
                .Columns("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    
                .Range("A2:B2").Delete Shift:=xlUp
    
                With .Range("B2", .Range("B2").End(xlDown)).Offset(, 1)
    
                    .FormulaR1C1 = "=INDEX('BO Overall Data'!C6, MATCH(RC1,'BO Overall Data'!C7,0) + COUNTIF('BO Overall Data'!C7, RC1) - 1)"
    
                    .Value = .Value
    
                End With
    
                .Range("C1").Value = "Most Recent Manager"
    
                .Columns("C").AdvancedFilter Action:=xlFilterCopy, _
    
                                             CopyToRange:=.Range("D1"), _
    
                                             Unique:=True
    
                .Range("D1").Value = "Unique Manager List"
    
     
    
                With .Range("C1:D1")
    
                    .Interior.Pattern = xlSolid
    
                    .Interior.Color = 65535
    
                    .Font.Bold = True
    
                End With
    
     
    
                Worksheets("BO Ref List").Range("D1:D200").Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    
                False, Orientation:=xlTopToBottom
    
                Worksheets("BO Ref List").Range("A:C").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    
                False, Orientation:=xlTopToBottom
    
        End With
    
    ' FO Section
    
    Workbooks("P-score and Q-score trends.xlsx").Worksheets("FO Ref List").Activate
    
        With Workbooks("P-score and Q-score trends.xlsx")
    
            .Worksheets("FO Ref List").Cells.ClearContents
    
            .Worksheets("FO Overall Data").Columns("G:H").Copy
    
            With .Worksheets("FO Ref List")
    
                .Range("A1").PasteSpecial
    
                .Columns("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    
                .Range("A2:B2").Delete Shift:=xlUp
    
                With .Range("B2", .Range("B2").End(xlDown)).Offset(, 1)
    
                    .FormulaR1C1 = "=INDEX('FO Overall Data'!C6, MATCH(RC1,'FO Overall Data'!C7,0) + COUNTIF('FO Overall Data'!C7, RC1) - 1)"
    
                    .Value = .Value
    
                End With
    
                .Range("C1").Value = "Most Recent Manager"
    
                .Columns("C").AdvancedFilter Action:=xlFilterCopy, _
    
                                             CopyToRange:=.Range("D1"), _
    
                                             Unique:=True
    
                .Range("D1").Value = "Unique Manager List"
    
     
    
                With .Range("C1:D1")
    
                    .Interior.Pattern = xlSolid
    
                    .Interior.Color = 65535
    
                    .Font.Bold = True
    
                End With
    
     
    
                Worksheets("FO Ref List").Range("D1:D200").Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    
                False, Orientation:=xlTopToBottom
    
                Worksheets("FO Ref List").Range("A:C").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    
                False, Orientation:=xlTopToBottom
    
            End With
    
        End With
    
    End With
    
    Workbooks("P-score and Q-score trends.xlsx").Worksheets("BO Occurance Trend").PivotTables("BO").PivotCache.Refresh
    
    Workbooks("P-score and Q-score trends.xlsx").Worksheets("FO Occurance Trend").PivotTables("FO").PivotCache.Refresh
    
    Dim r As Range
    
    Worksheets("BO Ref List").Activate
    
        Set r = Workbooks("P-score and Q-score trends.xlsx").Worksheets("BO Ref List").Range("B:D")
    
            r.Value = Evaluate("IF(ISNUMBER(" & r.Address & ")," & r.Address & ",PROPER(" & r.Address & "))")
    
    Worksheets("FO Ref List").Activate
    
        Set r = Workbooks("P-score and Q-score trends.xlsx").Worksheets("FO Ref List").Range("B:D")
    
            r.Value = Evaluate("IF(ISNUMBER(" & r.Address & ")," & r.Address & ",PROPER(" & r.Address & "))")
    
    Windows("P-score and Q-score trends.xlsx").Activate
    
        With ActiveWorkbook.Names("BOAssociate")
    
            .Name = "BOAssociate"
    
            .RefersToR1C1 = _
    
            "=OFFSET('BO Ref List'!RC[-2],0,0,COUNTA('BO Ref List'!R2C2:R989C2),1)"
    
            .Comment = ""
    
        End With
    
    Workbooks("Macros.xlsm").Close
    
    Workbooks("P-score and Q-score trends.xlsx").Active
    
    MsgBox ("Complete")
    
    End Sub
    Last edited by Dulanic; 04-13-2010 at 11:47 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1