Hi,

I have a problem, I have a worksheet which changes shape quite a bit
when different data is loaded into it and I need to be able to locate
cells so I can use formula on them as part of a larger macro.

How can I locate the cell which is two to the left of the cell which
will always contain the words 'Total Life Sales'.

I was thinking this is something to do with an active cell value =
'Total Life Sales' and then offset two the the left, but i'm not sure
how to phrase it.

Any help would be grand

Cheers

Hayley


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=541618

If you haven't found your answer yet, you could try something like this:

Dim rngCell As Range
With Sheet1
Set rngCell = .Cells.Find( _
What:="Total Life Sales", _
After:=.Range("A1"), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext _
).Offset(0, -2)
End With

You can then use rngCell as the cell that is 2 left of "Total Life Sales"
(ex. rngCell.Select or rngCell.Value = ""). Note that if it does not find
"Total Life Sales", it will give you a runtime error.

"Hru48" wrote:


Hi,

I have a problem, I have a worksheet which changes shape quite a bit
when different data is loaded into it and I need to be able to locate
cells so I can use formula on them as part of a larger macro.

How can I locate the cell which is two to the left of the cell which
will always contain the words 'Total Life Sales'.

I was thinking this is something to do with an active cell value =
'Total Life Sales' and then offset two the the left, but i'm not sure
how to phrase it.

Any help would be grand

Cheers

Hayley


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=541618

Try this:
If activecell.value = "Total Life Sales" then
Activecell.offset(0,-1).select
End if




"Hru48" wrote:


Hi,

I have a problem, I have a worksheet which changes shape quite a bit
when different data is loaded into it and I need to be able to locate
cells so I can use formula on them as part of a larger macro.

How can I locate the cell which is two to the left of the cell which
will always contain the words 'Total Life Sales'.

I was thinking this is something to do with an active cell value =
'Total Life Sales' and then offset two the the left, but i'm not sure
how to phrase it.

Any help would be grand

Cheers

Hayley


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=541618

Thats great - many many thanks


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=541618

It doesn't seem like my previous reply posted properly...

I wanted to let you know that if you haven't found an answer yet, you can
try something like this:

Dim rngCell As Range
With Sheet1 'Change this to the appropriate sheet name
Set rngCell = .Cells.Find( _
What:="Total Life Sales", _
After:=.Range("A1"), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext _
).Offset(0, -2)
End With

You can then use rngCell as the cell that's 2 left of "Total Life Sales".
Note that if it does not find "Total Life Sales", you will get a runtime
error.

"Hru48" wrote:


Hi,

I have a problem, I have a worksheet which changes shape quite a bit
when different data is loaded into it and I need to be able to locate
cells so I can use formula on them as part of a larger macro.

How can I locate the cell which is two to the left of the cell which
will always contain the words 'Total Life Sales'.

I was thinking this is something to do with an active cell value =
'Total Life Sales' and then offset two the the left, but i'm not sure
how to phrase it.

Any help would be grand

Cheers

Hayley


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=541618