Hi,
Can anyone help me with this problem?

I have a column of data with mixture of text and number.
I want to convert the whole row to text instead to mixture.

I know in excel, to make a number to a text you have to add a ' infront
of the number.

Secondly in a particular worksheet, how to i write a code to change the
whole activecell data to convert to text?



Can anyone help me?


Thank alot


--
brucelim80
------------------------------------------------------------------------
brucelim80's Profile: http://www.excelforum.com/member.php...o&userid=32244
View this thread: http://www.excelforum.com/showthread...hreadid=521302

ActiveCell.Value = "'" & ActiveCell.Value

--
Regards,
Tom Ogilvy


"brucelim80" wrote
in message ...

Hi,
Can anyone help me with this problem?

I have a column of data with mixture of text and number.
I want to convert the whole row to text instead to mixture.

I know in excel, to make a number to a text you have to add a ' infront
of the number.

Secondly in a particular worksheet, how to i write a code to change the
whole activecell data to convert to text?



Can anyone help me?


Thank alot


--
brucelim80
------------------------------------------------------------------------
brucelim80's Profile:
http://www.excelforum.com/member.php...o&userid=32244
View this thread: http://www.excelforum.com/showthread...hreadid=521302

thank alot for your help


--
brucelim80
------------------------------------------------------------------------
brucelim80's Profile: http://www.excelforum.com/member.php...o&userid=32244
View this thread: http://www.excelforum.com/showthread...hreadid=521302

hi,

how do i write a code to select all rows from the same column and check
for numeric data and convert them to the string

For example

Columns(G:G).Select

.....


--
brucelim80
------------------------------------------------------------------------
brucelim80's Profile: http://www.excelforum.com/member.php...o&userid=32244
View this thread: http://www.excelforum.com/showthread...hreadid=521302

Hi Bruce,

Try:

'=============
Public Sub Tester02()

Dim rng As Range

Set rng = Columns("G:G").SpecialCells(xlConstants, xlNumbers)

rng.NumberFormat = "@"

End Sub
'<<=============


---
Regards,
Norman



"brucelim80" wrote
in message ...

hi,

how do i write a code to select all rows from the same column and check
for numeric data and convert them to the string

For example

Columns(G:G).Select

....


--
brucelim80
------------------------------------------------------------------------
brucelim80's Profile:
http://www.excelforum.com/member.php...o&userid=32244
View this thread: http://www.excelforum.com/showthread...hreadid=521302

Hi Bruce,

More robust would be:

'=============
Public Sub Tester02A()

Dim rng As Range

On Error Resume Next
Set rng = Columns("G:G").SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0

rng.NumberFormat = "@"

End Sub
'<<=============


---
Regards,
Norman

Hi Bruce,

Additionally, replace:

rng.NumberFormat = "@"


with

If Not rng Is Nothing Then rng.NumberFormat = "@"

--
---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Bruce,

More robust would be:

'=============
Public Sub Tester02A()

Dim rng As Range

On Error Resume Next
Set rng = Columns("G:G").SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0

rng.NumberFormat = "@"

End Sub
'<<=============


---
Regards,
Norman

hi Norman,

Thank for the great help.
I need another flavour from you.

How do i write a code? Such that it will automatically dectected the
last row of the column and perform a check on individual Cell's data. I
have tried using the method (range) that you had posted earlier witha
for loop to extracted the value, but it can't work.


Can you help me please?

Thank you


--
brucelim80
------------------------------------------------------------------------
brucelim80's Profile: http://www.excelforum.com/member.php...o&userid=32244
View this thread: http://www.excelforum.com/showthread...hreadid=521302

Hi Bruce,

Try something like:

'=============
Public Sub Tester()
Dim LastCell As Range
Const col As String = "A" '<<==== CHANGE

Set LastCell = Cells(Rows.Count, col).End(xlUp)

MsgBox LastCell.Value

End Sub
'<<=============

Change A to the column of inteest.


---
Regards,
Norman



"brucelim80" wrote
in message ...

hi Norman,

Thank for the great help.
I need another flavour from you.

How do i write a code? Such that it will automatically dectected the
last row of the column and perform a check on individual Cell's data. I
have tried using the method (range) that you had posted earlier witha
for loop to extracted the value, but it can't work.


Can you help me please?

Thank you


--
brucelim80
------------------------------------------------------------------------
brucelim80's Profile:
http://www.excelforum.com/member.php...o&userid=32244
View this thread: http://www.excelforum.com/showthread...hreadid=521302

Hi norman,
Thank for your help.

I tired the code as given. It work fine if my column data is number.
However, if my column data is Text then it will give a mismatch type
error.


The code will detect the last cell data instead of the lastrow number.
Norman, can you help me again ?

For example my last row cell of the column contain the value "england"
LastCell = "england"



Thank alot


--
brucelim80
------------------------------------------------------------------------
brucelim80's Profile: http://www.excelforum.com/member.php...o&userid=32244
View this thread: http://www.excelforum.com/showthread...hreadid=521302

Hi Bruce,

I tired the code as given. It work fine if my column data is number.
However, if my column data is Text then it will give a mismatch type
error.

I do not understand. Show the exact code you are using.

The code will detect the last cell data instead of the lastrow number.
Norman, can you help me again ?

For example my last row cell of the column contain the value "england"
LastCell = "england"

Try:

'=============
Public Sub Tester2()
Dim LastCell As Range
Dim LastRow As Long
Dim LastValue As Variant
Const col As String = "A" '<<==== CHANGE

Set LastCell = Cells(Rows.Count, col).End(xlUp)

LastRow = LastCell.Row
LastValue = LastCell.Value

MsgBox "The last populated cell in column " _
& col & " is " & LastCell.Address(0, 0) _
& vbNewLine & "The corresponding row number is " _
& LastRow & vbNewLine _
& "The cell's value is " & LastValue

End Sub
'<<=============


---
Regards,
Norman



"brucelim80" wrote
in message ...

Hi norman,
Thank for your help.

I tired the code as given. It work fine if my column data is number.
However, if my column data is Text then it will give a mismatch type
error.


The code will detect the last cell data instead of the lastrow number.
Norman, can you help me again ?

For example my last row cell of the column contain the value "england"
LastCell = "england"



Thank alot


--
brucelim80
------------------------------------------------------------------------
brucelim80's Profile:
http://www.excelforum.com/member.php...o&userid=32244
View this thread: http://www.excelforum.com/showthread...hreadid=521302

Sorry to interrupt. Came across this Developersdex forum
somehow via Google while looking for way to convert
numbers (actually, dollar sums) to text.

Can anyone help me to write a simple macro (VB6/Word 2003/WinXP Pro SP2)
to convert the sum in a Word
Table 'Totals' cell to words?

The current macro I pieced together sums the right column
plus calculates tax and enters the total. Then it jumps directly
2 cells to left into a long row with room to enter the total
in 'words/text'. Currently it just ends there by highlighting
some dummy text in the row, ready for manually typing in the
figure.

If the number total is, for example, $150.60, I would manually
type in "One Hundred fifty and 60/100". However, the conversion need
not necessarily include the 60/100 (cents).
This latter could be typed in manually, as long as the macro ignores the
numbers to right of decimal point in the sum cell of the Word Table.

Perhaps I should post this in a new thread. I just registered here and
not sure yet how to do things. Saw this thread and thought it was as
close to what I'll ever get.

Appreciate any help at all on the subject. Am not a programmer; just an
old dabbler with quite a bit of experience in DOS batch files and just a
tad of VisBasic.

Jed



*** Sent via Developersdex http://www.developersdex.com ***