get the range one cell to the left of a find

[stuck]

The line:

Code: Select all

Set rngFound = Range("A2:AAA2").Find(myVariable)

gives the exact range of what I'm looking for but in fact I want the range of the cell immediately to the left of that range. For example, if the FIND returns the range "DN2", I want rngFound to become "DM2".

How do I do that?

Thanks,

Ken

[StuartR]

Set rngFound = Range("A2:AAA2").Find(myVariable).Offset(0, -1)

[stuck]

Yes

I realised as soon as I posted the question

Sorry it didn't dawn on me before you replied.

Ken

[StuartR]

I find that the lounge often works like that. I don't see the obvious answer to my own question until after I post it.

[HansV]

It's called POP: the Power Of Posting.

[Doc.AElstein]

I get that POP a lot, usually after I spent a lot of time preparing a beautiful post, and just before I post. It’s often quite disappointing because I don’t get then to post, and the world is deprived of yet another of my lovely postings…
:(

Hello ken,
There is another strange syntax alternative way to do the same as what you want.
Set rngFound = Range("A2:AAA2").Find(myVariable).Item(1, 0) ' (0, 0)is one up, one left (1, 0)is same row, one back left
The Item property lets you go back left or back up, so it will take –ve numbers. It “goes through the origin”, so like (0, 0) would be (one up, one to the left)
( This alternative way of doing offset only returns you one cell, - it is not offsetting your range, its giving you a single cell, relative to the top left of the range its applied to, regardless of whether that range is a single or multi cell range.
(1, 1) will be either your cell for a single cell range, or the top left cell if your range is a multi cell range )

Alan

[stuck]

...There is another strange syntax alternative way to do the same as what you want...

Thanks but in my experience simple is usually best so I'll stick with the simple and more obvious:

Code: Select all

.Offset(0-1)

Ken

[Doc.AElstein]

was just out of interest, - Offset sounds better to me as well, for offsetting.
(The only use as an alternative I can think of might be if you always wanted to get just the top left cell offsetting from a range of any size - doing it like the item alternative way, it would save you having to Resize to a single cell after the offset.
But then I might be inclined to do it with the extra resizing, as it might look more understandable, and beautiful
All out of just passing interest , that's all. :)