Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying diagonally opposite value from the known cell/range.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying diagonally opposite value from the known cell/range.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to split a cell diagonally? | Excel Discussion (Misc queries) | |||
Return Value in Cell Diagonally opposite | New Users to Excel | |||
split a cell diagonally in excell - a calendar -2 dates in 1 cell | Excel Discussion (Misc queries) | |||
How do I divide a cell into two diagonally? | Excel Discussion (Misc queries) | |||
How do I split a cell diagonally? | Excel Discussion (Misc queries) |