PDA

View Full Version : Message Box for Column



randolphoral
01-07-2010, 07:06 AM
I use the code below to convert cell values that are text to numbers.

Since I use this on multiple columns and each column has a different range I am trying to add a message box that will open when the macro is played and ask for the Column letter or Number.

I am not sure where to begin to do something like this.


Sub ConvertToNumbers()
Application.ScreenUpdating = False
For Each cell In Range("E1:E1000")
If Not IsEmpty(cell.Value) Then
cell.Select
Cells.SpecialCells(xlCellTypeLastCell) _
.Offset(1, 1).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
With Selection
.VerticalAlignment = xlTop
.WrapText = False
End With
Selection.EntireColumn.AutoFit
End If
Next


End Sub

Bob Phillips
01-07-2010, 07:53 AM
What is that code supposed to be doing? I am confused by the SpecialCells bit.

randolphoral
01-07-2010, 08:17 AM
What is that code supposed to be doing? I am confused by the SpecialCells bit.

The code goes through E1:E1000 and looks for all non empty cells and converts the cell values from text to numbers.

The Special Cells portion is referring to the last used cell in range
I have included the link for information on the SpecialCells Method
http://msdn.microsoft.com/en-us/library/aa213567(office.11).aspx

Bob Phillips
01-07-2010, 08:44 AM
Yes, but you select the cell beforehand, so the last cell is that cell.

RolfJ
01-07-2010, 09:02 AM
If I understand you correctly you would just like to add a user input box to your code that allows the user to select a particular column. If that is indeed the case and without questioning your use of the SpecialCells method I would like to suggest the following code. Please take a look and see whether it meets your needs:


Sub ConvertToNumbers()

Dim strColumn As String
Dim testCell As Range
strColumn = _
InputBox("Enter Column ID (e.g. E or AE)", _
"Please choose column to convert", "E")
Do
On Error Resume Next
Set testCell = Columns(strColumn).Cells(1, 1)
If Not testCell Is Nothing Then
Exit Do
Else
strColumn = _
InputBox("Enter Column ID (e.g. E or AE)", _
"You entered an invalid column ID. Please try again.", "E")
End If
Loop
On Error GoTo 0

Dim cell As Range
Application.ScreenUpdating = False
For Each cell In _
Range(Columns(strColumn).Cells(1, 1), Columns(strColumn).Cells(Rows.Count, 1).End(xlUp))
If Not IsEmpty(cell.Value) Then
cell.Select
Cells.SpecialCells(xlCellTypeLastCell) _
.Offset(1, 1).Copy
With Selection
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
.VerticalAlignment = xlTop
.WrapText = False
End With
End If
Next
End Sub

randolphoral
01-07-2010, 09:26 AM
That is exactly what I was looking for. Nice Job! Thank you so much for your help on this...you have made my week you have no idea how many times a day I found myself having to edit the code for the particular column.