VBA: Type mismatch in dynamic range reference

February 7, 2024

I am very green in VBA and have googled my way to this point. It’s been a great learning experience, and many thanks to all you good people in stack overflow.

I’m trying to have my macro 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.

So far my code will select the correct row, and the first empty cell in a defined row, but I can’t get it to do both together without returning an error. It’s returning 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 proper way to make it:
.value = ThisWorkbook.Sheets("data input").Range("B1")

I have tried to change the variable for rw, cl, and clm; use listObjects.ListRows; save/closing/opening to try to force a cache reset; and a million other things. I’ve had success from running the rw and clm lines separately, but it just generates different errors together.

I don’t have the base knowledge of how VBA works at this point to understand the problem entirely. Any assistance would be appreciated.

>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