[SOLVED] Type mismatch in dynamic range reference

Type mismatch in dynamic range reference

I'm trying to find a row in a table based on a name from a data input sheet, then find the first open cell in the row, and finally copy a date from the data input sheet into that cell.

My code will select the row, and the first empty cell in a defined row.

I can't get it to do both together. It returns a "Type-mismatch" error for the cl line.

Sub TestModule2()
 
    Dim sh As Worksheet
    Dim rw As Object
    Dim clm As Object
    Dim cl As Object
 
    Set sh = ThisWorkbook.Sheets("database")
    Set rw = sh.Range("A:A").Find(ThisWorkbook.Sheets("data input").Listobjects("Table1").Range(2, 1).Value)
    Set clm = sh.Range("Table35").Rows.Find(What:="", LookIn:=xlValues, LookAt:=xlWhole)
    Set cl = sh.Listobjects("Table35").Range(rw, clm)
 
    cl.Select
 
End Sub

I'm using cl.Select for testing, the next challenge is to figure out the way to make it:

.value = ThisWorkbook.Sheets("data input").Range("B1")

I tried to change the variable for rw, cl, and clm; use listObjects.ListRows; save/closing/opening to try to force a cache reset.

I had success running the rw and clm lines separately.

Solution

rw is a range object.
rw.EntireRow or sh.Rows(rw.Row) is the row range object of the cell rw.
Always use LookIn:=..., LookAt:=... arguments in Find clause.

Sub TestModule2()
    Dim sh As Worksheet
    Dim rw As Range
    Dim clm As Range
    Dim cl As Range
    Set sh = ThisWorkbook.Sheets("database")
    Set rw = sh.Range("A:A").Find(ThisWorkbook.Sheets("data input").ListObjects("Table1").Range(2, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
    If rw Is Nothing Then Exit Sub
    Set clm = rw.EntireRow.Find(What:="", LookIn:=xlValues, LookAt:=xlWhole)
    If clm Is Nothing Then Exit Sub
    clm.Value = ThisWorkbook.Sheets("data input").Range("B1").Value
End Sub