Data Types in Cells

[BrianMH]

Hi,

This isn't a question but a quick solution. It has probably been done before but I did it today because people at work needed it and thought I would share it with the rest of the world.

Sometimes when get data from another sheet or paste it from a report and stuff it comes over as just plain text and not what you would like. For instance a date has a space and you can't change the format or use it in a formula because it just doesn't show as a date. The same goes for currencies and stuff. Often you can just double click the cell and it self corrects. No one wants to double click 100 cells though if its a whole column. This simple macro I have put in my personal workbook file and assigned it to ctrl+t. Now I can select all the cells I want changing to another type and it pops up with a selection to change to date,long,decimal or currency.

I hope others find this helpful.

[VBA]
Sub trimmer()
Dim rSel As Range
Dim intConv As Integer
Dim c as Range

Set rSel = Selection
Application.ScreenUpdating = False
intConv = Application.InputBox("What would you like to convert to? Please enter a number" & Chr(13) & _
"1. Date" & Chr(13) & _
"2. Currency" & Chr(13) & _
"3. Decimal" & Chr(13) & _
"4. long (whole number)", , , , , , , 1)
Select Case intConv
Case 1
For Each c In rSel.Cells
If c.Value <> "" Then
c.Value = CDate(c.Value)
End If
Next c
Case 2
For Each c In rSel.Cells
If c.Value <> "" Then
c.Value = CCur(c.Value)
End If
Next c
Case 3
For Each c In rSel.Cells
If c.Value <> "" Then
c.Value = CDec(c.Value)
End If
Next c
Case 4
For Each c In rSel.Cells
If c.Value <> "" Then
c.Value = CLng(c.Value)
End If
Next c
Case False
MsgBox ("you did not select a conversion type")
End Select
Application.ScreenUpdating = True
End Sub
[/VBA]

[mdmackillop]

Hi Brian,
Welcome to VBAX
Thanks for the code. When we get the KB sorted after the recent hiatus, you could consider submitting it there.
When you post code, use the green VBA button to insert VBA tags rather than Code tags to format it as shown.
Regards
MD

ps I Dimmed "c" as well!

[BrianMH]

Ah thanks. I learned coding purely from the help files so I have picked up a few bad habits that I am trying to break..such as forgetting to dim things .

[mdmackillop]

Always use Option Explicit. You can set up your workbooks to add it automatically
In VBE Editor Tools/Options/Editor "Require variable declaration" should be ticked.