Convert column to text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex St-Pierre - 21 Feb 2006 23:26 GMT

Hello,
I want to convert a column with "SSN" title in text but I don't know where
the column is located. The programation below gives me the first column wich
contains SSN title. Does anyone know how to find an exact match of the SSN
title ?
Thanks!
} Set rng = wks.Rows(1).Find(What:="SSN")
} If Not rng Is Nothing Then rng.EntireColumn.TextToColumns Cells(1,
rng.Column)

Signature

Alex St-Pierre

Reply to this Message

Jim Thomlinson - 21 Feb 2006 23:31 GMT

Find takes a number of arguments. Add this argument and it will require an
exact match...

Set rng = wks.Rows(1).Find(What:="SSN", LookAt:=xlWhole)

Signature

HTH...

Jim Thomlinson

> Hello,
> I want to convert a column with "SSN" title in text but I don't know where
[quoted text clipped - 5 lines]
> } If Not rng Is Nothing Then rng.EntireColumn.TextToColumns Cells(1,
> rng.Column)

Reply to this Message

Don Guillett - 21 Feb 2006 23:42 GMT

try
rng = wks.Rows(1).Find(What:="SSN").column

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hello,
> I want to convert a column with "SSN" title in text but I don't know where
[quoted text clipped - 6 lines]
> } If Not rng Is Nothing Then rng.EntireColumn.TextToColumns Cells(1,
> rng.Column)

Reply to this Message

Dave Peterson - 21 Feb 2006 23:42 GMT

Record a macro when you do the .find manually. You'll see one of the options is
to look at the whole cell.

LookAt:=xlWhole

I think you'll find it better to specify all the parms that you can. Excel/VBA
will remember the last settings used--no matter if the user did it or your code
did it.

with wks.rows(1)
set rng = .Find(What:="SSN", _
After:=.cells(.cells.count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)

I specified match case and whole cell.

> Hello,
> I want to convert a column with "SSN" title in text but I don't know where
[quoted text clipped - 7 lines]
> --
> Alex St-Pierre

Signature

Dave Peterson

Reply to this Message