I have 2 columns, one with a list of cell references (P), one with text (Q). I am trying to get the text to be put in the particular cell reference from a macro.

The code I’m using keeps throwing a ‘Run-time error 1004, Method ‘Range’ of object ‘_Worksheet’ failed

Sub OverwriteCellReferencesWithNames()
    Dim ws As Worksheet
    Dim cell As Range
    Dim nameCell As Range
    Dim cellRef As String
    
    ' Set worksheet reference to Sheet1
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Clear the contents of the specified ranges
    ws.Range("B4:F8").ClearContents
    ws.Range("B11:F15").ClearContents
    
    ' Loop through each cell in column P
    For Each cell In ws.Range("P2:P" & ws.Cells(ws.Rows.Count, "P").End(xlUp).Row)
        ' Get the cell reference from column P
        
        cellRef = cell.Value
        
        ' Find the corresponding name in column Q
        Set nameCell = ws.Cells(cell.Row, "Q")
        
        ' If a corresponding name is found in column Q, overwrite the cell reference in column P with the name

        If Not nameCell Is Nothing Then
            ws.Range(cellRef).Value = nameCell.Value
        End If
    Next cell
End Sub

The line that that keeps throwing the error is the “ws.Range(cellRef).Value = nameCell.Value”

Any help would be appreciated!

I have tried error checking to force the code to only work when the format of cellRef is appropriate ie. a string with a proper cell address. I expected this to exit the for loop when it discovered a new cell with no text in it, however, it still threw an error.