VBA - Find and Return Cell Range (Row)

Bablu

Board Regular
Joined
Dec 9, 2008
Messages
126
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,233
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top