Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

All of a sudden, this line of code is now giving me an error ' If Evaluate("ISREF('" &…

  1. User

    All of a sudden, this line of code is now giving me an error ' If Evaluate("ISREF('" & observeSheet.Range("B" & i).Value & "'!A1)") Then '

    in the following code: Sub CopyAndSortInfo() ' Define Variables Dim observeSheet As Worksheet Dim wb As Workbook Dim LastRow As Long Dim i As Long

    ' Set Workbook Object Set wb = ThisWorkbook

    ' Set Observe Sheet Object Set observeSheet = wb.Sheets("Observe")

    ' Get the Last Row Number with Value in Column B LastRow = observeSheet.Cells(Rows.Count, "B").End(xlUp).Row

    ' Loop Through the Rows For i = 2 To LastRow ' Check If Sheet Name Exists If Evaluate("ISREF('" & observeSheet.Range("B" & i).Value & "'!A1)") Then ' Get the Destination Sheet Object Dim destSheet As Worksheet Set destSheet = wb.Sheets(observeSheet.Range("B" & i).Value) ' Get the Last Row Number with Value in Column A Dim destLastRow As Long destLastRow = destSheet.Cells(Rows.Count, "A").End(xlUp).Row ' Copy The Observation to the Destination Sheet destSheet.Range("A" & destLastRow + 1).Value = observeSheet.Range("A" & i).Value ' Sort The Destination Sheet by Priority Number in Column C destSheet.Sort.SortFields.Clear destSheet.Sort.SortFields.Add2 Key:=Range("C2:C30"), SortOn:=xlSortOnValues destSheet.Sort.SortFields(1).Order = xlAscending destSheet.Sort.Apply End If Next i

    Application.Wait (Now + TimeValue("0:00:02"))

    Call CopyPremValues

    End Sub

  2. Assistant

    The error is likely due to a change in the structure or naming of the worksheets in the workbook. The code uses the value in column B of the "Observe" sheet to reference a worksheet with that name, but if that worksheet doesn't exist or has been renamed, the code will throw an error. Double check the worksheet names and ensure they match the values in column B.

  3. User

    sheet name has not changed

  4. Assistant

    In that case, the error may be due to a change in the workbook structure or settings that is affecting the Evaluate function. Here are a few things you can try:

    1. Make sure that the workbook containing the sheets you are referencing is open. The Evaluate function may not work properly if the referenced workbook is not open.

    2. Check if any of the sheet names in the workbook contain special characters or spaces. If so, try removing them and see if the problem persists.

    3. Try replacing the Evaluate function with a WorksheetFunction that checks if a worksheet exists. Here's an example:

    If Not WorksheetFunction.IsError(Application.WorksheetFunction.Match(observeSheet.Range("B" & i).Value, wb.Sheets, 0)) Then
    

    This code checks if the sheet name in column B exists in the workbook by using the Match function on the collection of sheet names in the workbook's Sheets property.

    1. Check if there are any missing references or corrupted libraries in the workbook. Go to the VBA Editor, click Tools -> References, and look for any libraries that are marked as "Missing". If you find any, try fixing the references or removing the library if it's not needed.

    I hope one of these steps helps resolve the issue.