VBA - Find and Return Cell Range (Row)

[Bablu]

Hi All,

I been attempting to modify a code, below:

My issue is here:Qfooter = foundcell.Value, instead of the just the value, I would like to pick up the entire range.

Any help would be great. Thanks in advance!

Sub Find_And_Return_cell_value()
On Error Resume Next
For Each cll In Selection.SpecialCells(xlCellTypeVisible).Cells
Rfooter = Empty
datatoFind = cll.Value
If datatoFind <> "" Then
If Not IsError(CDbl(datatoFind)) Then datatoFind = CDbl(datatoFind)
For Each Sht In ActiveWorkbook.Sheets
If Not Sht Is ActiveSheet Then
Set foundcell = Sht.Cells.Find(what:=datatoFind, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not foundcell Is Nothing Then


Qfooter = foundcell.Value <----- Change to pick the entire range, not just the value

Cells(cll.Row, Columns.Count).End(xlToLeft).Offset(, 1) = Qfooter
Exit For
End If
End If
Next
End If
Next
End Sub

Thanks
Bablu