Recognising Text formatted cells in code
 

Need some help please.

I have a Worksheet which has columns formatted into either Text or
Numeric cells.

I want to:

1) Assign a range in the worksheet (e.g. $a2:$BK2).

2) Recognise the cell format of each column in that range (e.g. whether
the column format is Text or Numerical)

3) Where the column is Text formatted, take the value of each cell in
that column and add quotation marks around the value (e.g. where the
value is MONDAY, add quotation marks around MONDAY to change it to
"MONDAY").

This is so I can save the worksheet as a .CSV file where text values
are shown in quotation marks when opened as a text file(e.g.
"DM",100000,100000,"WM2","FIRM")

Any route as long as the solution is reached will be fine.

Hope I can get some help with the complete solution or at least part of
it. Many thanks in advance.

Pini



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Recognising Text formatted cells in code
 

Pini,

Sub Pini()
range(a2).select
do until activecell.address="BK2"
if isnumber(activecell.value) then
else
activecell.value="""&activecell.value&"""
end if
activecell.offset(0,1).select
loop

may help

Steve
"pini35" wrote in message
...
Need some help please.

I have a Worksheet which has columns formatted into either Text or
Numeric cells.

I want to:

1) Assign a range in the worksheet (e.g. $a2:$BK2).

2) Recognise the cell format of each column in that range (e.g. whether
the column format is Text or Numerical)

3) Where the column is Text formatted, take the value of each cell in
that column and add quotation marks around the value (e.g. where the
value is MONDAY, add quotation marks around MONDAY to change it to
"MONDAY").

This is so I can save the worksheet as a .CSV file where text values
are shown in quotation marks when opened as a text file(e.g.
"DM",100000,100000,"WM2","FIRM")

Any route as long as the solution is reached will be fine.

Hope I can get some help with the complete solution or at least part of
it. Many thanks in advance.

Pini



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Recognising Text formatted cells in code
 

Thanks for the reply.

Tried the code, but I get a Compile Error message with *IsNumber*.

Also, does this check to see if the value is numerical or the cell
format? There will be occasions where numeric values will be
represented as text.

If there is no way around this, any other suggested scripts will be
more than welcome.

Thanks again.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Recognising Text formatted cells in code
 

That solution won't work. If you put in quotes on each end, you will get two
sets of quotes in the csv file.

"Monday"

will be

""Monday"" in the csv file.

You will have to write out your file using VBA.

http://support.microsoft.com/default...48&Product=xlw
XL2000: Procedure to Export a Text File with Both Comma and Quote Delimiters

http://support.microsoft.com/default...96&Product=xlw
XL2002: Procedure to Export a Text File with Both Comma and Quote Delimiters

--
Regards,
Tom Ogilvy


pini35 wrote in message
...
Need some help please.

I have a Worksheet which has columns formatted into either Text or
Numeric cells.

I want to:

1) Assign a range in the worksheet (e.g. $a2:$BK2).

2) Recognise the cell format of each column in that range (e.g. whether
the column format is Text or Numerical)

3) Where the column is Text formatted, take the value of each cell in
that column and add quotation marks around the value (e.g. where the
value is MONDAY, add quotation marks around MONDAY to change it to
"MONDAY").

This is so I can save the worksheet as a .CSV file where text values
are shown in quotation marks when opened as a text file(e.g.
"DM",100000,100000,"WM2","FIRM")

Any route as long as the solution is reached will be fine.

Hope I can get some help with the complete solution or at least part of
it. Many thanks in advance.

Pini



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Recognising Text formatted cells in code
 

Found the cell format code that tells you if a cell is text formatted.

*if Selection.Cells(rowcount, columncount).NumberFormat = "@"* acts on
cells that are text formatted (@ is the code for a text cell)


Thanks for all your help. The code at
http://support.microsoft.com/defaul...amp;Product=xlw was especially
helpfull. I have modified it to meet my requirements.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/