I am getting a type Mismatch when I run this code: Sub SearchCellValueLinkedDocs()…
- 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.SearchMODLinkedDocsEnd Sub
- Assistant
The type mismatch error is likely occurring when trying to assign the value of
cell.ValuetolinkedDocsWs.Cells(lastRow, "D").ValueorlinkedDocsWs.Cells(lastRow, "E").Value.To fix this issue, you can modify the code to check if
cell.Valueis 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 cellBy adding the
If Not IsEmpty(cell.Value) Thencondition, it ensures that only non-empty cells are being assigned tolinkedDocsWs.Cells(lastRow, "D").ValueandlinkedDocsWs.Cells(lastRow, "E").Value.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.