Recognising Text formatted cells in code

[pini35]

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

 

[Steve Smallman]

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]

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.

 

[Tom Ogilvy]

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.aspx?scid=kb;en-us;213448&Product=xlw
XL2000: Procedure to Export a Text File with Both Comma and Quote Delimiters

http://support.microsoft.com/default.aspx?scid=kb;en-us;291296&Product=xlw
XL2002: Procedure to Export a Text File with Both Comma and Quote Delimiters

 

[pini35]

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.