CODEWITHSUNDEEP
excelvbamacos
jorddster
jorddster

Reputation: 15

How to set a Range variable using .Find?

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.

Upvotes: 0

Views: 230

Answers (1)

dwirony
dwirony

Reputation: 5450

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

Upvotes: 1

Related Questions