Cells.Find(What:=???

[george hart]

Hi

Can anyone advise why the Cells.Find(What:=(r) is blank, when it should be looking for the contents i copied?


ActiveCell.Offset(-1, 0).Select
Selection.Copy
Selection = r
Application.CutCopyMode = False
Sheets("Current Period-YTD Ops KPI").Select
Cells.Find(What:=(r), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate


Man thanks in advance

 

[jasonb75]

Because r is undefined.

Selection = r

Writes the value of r to the selected cell, not the value of the cell to r.

Dim r As Variant
r = ActiveCell.Offset(-1,0).Value
 
Sheets("Current Period-YTD Ops KPI").Select
Cells.Find(What:=(r), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

You probably don't need to select the sheet or cell, depends what you want to do next.

I've declared r as Variant, note that this is not strictly correct, ideally it should be declared based on the type of data used.

 

[george hart]

Many thanks - worked a treat.