Copying diagonally opposite value from the known cell/range.

[Marvin]

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]

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!)