BrianMH
02-18-2009, 10:12 AM
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.
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
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.
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