Dim rng as Range
With Sheets("Sheet1").Range("A1:Z200")
Set rng = .Find(What:="Name of the person", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
if rng is nothing then
msgbox "Not found"
else
if rng.column = 1 _
or rng.row = 1 then
msgbox "No cell above or to the left!"
else
sheets("Sheet1").range("z2").value = rng.offset(-1,-1).value
end if
end if
(Untested, uncompiled. Watch for typos!)
On 08/26/2010 07:24, Marvin wrote:
> Hello all,
>
> The address of the cell that has the keyword I'm interested in, will
> float when rows are added or deleted before its row. For example, if
> the keyword is in cell B46, it will move to B48 when two rows are
> added before the 46th row.
>
> I have the following routine to find my keyword "Name of the person"
> which currently resides in cell B46 in Sheet1.
>
> Dim rng as Range
> With Sheets("Sheet1").Range("A1:Z200")
> Set rng = .Find(What:="Name of the person", _
> After:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> End With
>
> I'm stuggling to accomplish the following, though.
>
> Once the keyword is found, I want the macro/routine to find& copy the
> value of the cell diagonally opposite to it in the left and paste the
> value in cell Z2. In the context of the above example, cell diagonally
> opposite in the left to B48 is A47. So, cell value of Z2 should be
> equal to value of the cell A47 in this case.
>
> How would I do that? Any help will be much appreciated.
>
> Thanks.
--
Dave Peterson
|
Similar Threads
