How do I change the datatype of a cell?

  • Thread starter Thread starter Casper Hornstrup
  • Start date Start date
C

Casper Hornstrup

I need to change the datatype of all cells in a column to text. I have the
following VBscript:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Call UpdateDatatypes
End Sub

Private Sub Workbook_Open()
Call UpdateDatatypes
End Sub

Private Sub UpdateDatatypes()
Dim myCell As Excel.Range
Dim myRng As Excel.Range
Dim wks As Excel.Worksheet

For Each wks In Me.Worksheets
Set myRng = wks.UsedRange.Cells

For Each myCell In myRng.Cells
If (Not Intersect(myCell, wks.Range("h:n")) Is Nothing) Then
myCell.Value = CStr(Replace(myCell.Value, ",", "."))
End If
If (Not Intersect(myCell, wks.Range("a:b")) Is Nothing) Then
myCell.Value = CStr(Replace(myCell.Value, ",", "."))
End If
Next
Next
End Sub

Now, any cells that have only digits in them will be converted to a number
type by Excel.
If I use:

myCell.Value = "A" & CStr(Replace(myCell.Value, ",", "."))

then cells will have a text type after the script is run. How do I keep
excel from changing the
type of the cells with only digits in them to number?

I need this because the MS JET OleDb provider for excel is stupid and will
only allow
reading of a cell if it has the same type as the the cell in same column in
the previous row
(if not it returns an empty string).

Casper
 
Private Sub UpdateDatatypes()
Dim myCell As Excel.Range
Dim myRng As Excel.Range
Dim wks As Excel.Worksheet

For Each wks In ThisWorkbook.Worksheets
Set myRng = Intersect(wks.UsedRange, wks.Range("A:B,H:N"))

For Each myCell In myRng
myCell.NumberFormat = "@"
myCell.Value = "'" & CStr(Replace(myCell.Text, ",", "."))
Next
Next
End Sub

Assuming you still want to replace any comma with a period/fullstop
 
In the following macro to each value of the defined cells
is "'" added. Due to this it gets a string.

Sub MakeString()
x = 4
For i = 1 To x
Cells(i, 1).Value = "'" & Cells(i, 1).Value
Next
End Sub

Regards
Klaus
 
Thanks Klaus. This worked beatifully.

Also thanks to you all for the NumberFormat solution.
I'm sure I'm going to need that sometime.
 
Note that my code
myCell.Value = "'" & CStr(Replace(myCell.Text, ",", "."))
also included the single quote

just for interest.
 
Back
Top