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

I am getting a type Mismatch when I run this code: Sub SearchCellValueLinkedDocs()…

  1. User

    I am getting a type Mismatch when I run this code: Sub SearchCellValueLinkedDocs() 'SEARCHES CELLS IN ALL WORKSHEETS FOR LINK PATH TO G Dim wb As Workbook Dim linkedDocsWs As Worksheet Dim lastRow As Long Dim ws As Worksheet Dim cell As Range Dim addressPath As String

    Set wb = ThisWorkbook
    Set linkedDocsWs = wb.Sheets("Test Sheet")
    
    For Each ws In wb.Sheets
        If ws.Name <> "Test Sheet" And ws.Name <> "Linked Docs" Then
            For Each cell In ws.UsedRange
                If InStr(1, cell.Value, "G:\Shared drives") > 0 Then
                lastRow = linkedDocsWs.Cells(linkedDocsWs.Rows.Count, "D").End(xlUp).Row + 1
                    linkedDocsWs.Cells(lastRow, "D").Value = cell.Value
                    linkedDocsWs.Cells(lastRow, "E").Value = "'" & ws.Name
                    lastRow = lastRow + 1 ' Move to the next row in the new worksheet
                End If
            Next cell
        End If
    Next ws
    'Call Module32.SearchMODLinkedDocs
    

    End Sub

  2. Assistant

    The type mismatch error is likely occurring when trying to assign the value of cell.Value to linkedDocsWs.Cells(lastRow, "D").Value or linkedDocsWs.Cells(lastRow, "E").Value.

    To fix this issue, you can modify the code to check if cell.Value is not empty before assigning it to another cell. Here's the modified code:

    For Each cell In ws.UsedRange
        If Not IsEmpty(cell.Value) Then ' Check if cell value is not empty
            If InStr(1, cell.Value, "G:\Shared drives") > 0 Then
                lastRow = linkedDocsWs.Cells(linkedDocsWs.Rows.Count, "D").End(xlUp).Row + 1
                linkedDocsWs.Cells(lastRow, "D").Value = cell.Value
                linkedDocsWs.Cells(lastRow, "E").Value = "'" & ws.Name
                lastRow = lastRow + 1 ' Move to the next row in the new worksheet
            End If
        End If
    Next cell
    

    By adding the If Not IsEmpty(cell.Value) Then condition, it ensures that only non-empty cells are being assigned to linkedDocsWs.Cells(lastRow, "D").Value and linkedDocsWs.Cells(lastRow, "E").Value.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.