How to set a Range variable using .Find?

Question

I'm trying to set a range variable "rcell" equal to the range of a cell containing the value of variable "lot" by using the .Find function.

I want to search in a specific range (column D) instead of all cells.

I get a type mismatch error.

I tried using "Columns(4)" instead of "Range(D:D)".

Workbooks("lotworkbook.xlsx").Activate
Sheets("Sheet1").Select
lot = Cells(row, 3)
Workbooks("findworkbook.xlsx").Activate
Sheets(3).Select
            
Dim rcell As Range
Set rcell = Range("D:D").Find(What:=lot, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) 'this is the line with issues'
            
If rcell Is Nothing Then

Else

' ...

Expected results: Find cell with the value of variable "lot" in a certain sheet and find that value on another sheet, and set range variable "rcell" equal to that cell location.

Actual results: Type mismatch error. This was working with Cells.Find but not with Range("D:D").Find.

@Warcupine Still getting a type mismatch. Cells.Find was working, but after I adjusted it to only search in Range("D:D"), I'm getting a type mismatch error. — jorddster
@cybernetic.nomad row is defined earlier in the code as an integer. The code was working fine before I changed it to only search in Range D — jorddster
yeah I always forget without the set it defaults to .value my bad — Warcupine

dwirony dwirony · Accepted Answer · 2019-07-22T16:53:02

I believe the issue is After:=ActiveCell - remove this and try again:

Dim rcell As Range
Set rcell = Range("D:D").Find(What:=lot, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) 'this is the line with issues

How to set a Range variable using .Find?

1 Answers