VAB Find text in Col and copy the last cell in the row

[liwi2]

Hi All,
I hope you will be able to help me. I tried to look online for solution but no luck so far.

What I am trying to do is to search for text ie "Apple" in col A.
Once found I want to set the last used cell as source to replicate the value in destination.
I have the search bit which works but can't figure out how to get the last cell from range cel row.

Dim SrchRng As Range, cel As Range
Set SrchRng = WorkBk.Worksheets(1).Range("A1:A100")
For Each cel In SrchRng
If InStr(1, cel.Value, "Apple") > 0 Then

Thanks in advance for your help

 

[baitmaster]

easiest way would be to start at the far right of the row and jump back, like ctrl + arrow. You already know the row because you've found cel, so you can refer to the last used cell in the row using

WorkBk.Worksheets(1).Cells(cel.row,16384).End(xlToLeft)

 

[liwi2]

It worked It seems I was overcomplicating things when the solution is so simple

Thanks a lot