determine the column where a cell was found

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

usadream - 24 Mar 2006 12:20 GMT

Hi all,

I have a find that returns the range of a cell where the search-value
is found. does anyone know how i can determine the column where this
cell is located?

Code:
--------------------
Set teamNameRng = searchRng.Find(What:=tempTeamName, _
After:=startRng, _
Lookat:=xlWhole, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
--------------------

Than a lot in advance
regards
thomas

Signature

usadream

Reply to this Message

Norman Jones - 24 Mar 2006 12:36 GMT

Hi USADream,

Try:

Dim myCol as long

myCol = teamNameRng.Column

---
Regards,
Norman

> Hi all,
>
[quoted text clipped - 16 lines]
> regards
> thomas

Reply to this Message

usadream - 24 Mar 2006 14:24 GMT

Hi,

thanx a lot for your reply:
now ur solution returns my the number of the column, but in the end,
what i need is defining a range in this column, so for e.g. if

Code:
--------------------
myCol = teamNameRng.Column
--------------------
--> myCol = 11

then i should be able to define a range like this:

Code:
--------------------
Set destrange = activeSheet.Range("myCol7 : myCol146")
--------------------

where 7 and 146 are start and end rows..

does anyone know the syntax for this?

help apreciated, thanx
thomas

Signature

usadream

Reply to this Message

usadream - 24 Mar 2006 14:55 GMT

nevermind, i found it out :-)

Signature

usadream

Reply to this Message