VBA Find function "Object variable or With block variable not set"

Asked Mar 14 '22 at 11:35

Active Mar 14 '22 at 11:35

Viewed 216 times

When I try using the following code to find the address of specific cells in one workbook, and copy the address to another wb it works fine.

        Set ra_from = wb_from.Sheets("Core").Cells.Find(What:=cel_name, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        wb_macro.Sheets("Engine").Activate
        wb_macro.Sheets("Engine").Range(cel.Address).Offset(0, 3) = ra_from.Addres

However, I would like to search only in one column, so I switched Cells for Columns("B:B")

        Set ra_from = wb_from.Sheets("Core").Columns("B:B").Find(What:=cel_name, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        wb_macro.Sheets("Engine").Activate
        wb_macro.Sheets("Engine").Range(cel.Address).Offset(0, 3) = ra_from.Address

And I get the error "Object variable or With block variable not set". Does anyone know what might be the problem?

asked Mar 14 '22 at 11:35

mohamed123

What is `cel`? Is this defined or set somewhere? – FunThomas Mar 14 '22 at 11:39
`Set ra_from = wb_from.Sheets("Core").Columns("B:B").Cells.Find(W...` You forgot `.Cells.` – Ron Rosenfeld Mar 14 '22 at 11:41
Hi, [answer](https://stackoverflow.com/questions/14931700/how-to-find-a-value-in-an-excel-column-by-vba-code-cells-find) to your question is here :). – p77u77n77k Mar 14 '22 at 11:42
Cel is a Range (it is a cell). @ron unfortunately still getting the same error – mohamed123 Mar 14 '22 at 11:45
1

This only means that no match has been found for `cel_name` in the searched column. When stopped on error, please move the mouse over `ra_from`. Doesn't it show `Nothing`? – FaneDuru Mar 14 '22 at 11:48
Try adding `, After:=wb_from.Sheets("Core").Range("B1")`. And no need of `wb_macro.Sheets("Engine").Activate` – FaneDuru Mar 14 '22 at 11:55
3

Then it didn't find a match. – Ron Rosenfeld Mar 14 '22 at 11:59
Was pointing to the wrong column. Thanks Ron! – mohamed123 Mar 14 '22 at 12:18

VBA Find function "Object variable or With block variable not set"

0 Answers0