Message Box for Column

[randolphoral]

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]

What is that code supposed to be doing? I am confused by the SpecialCells bit.

[randolphoral]

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/libr...ffice.11).aspx

[Bob Phillips]

Yes, but you select the cell beforehand, so the last cell is that cell.

[RolfJ]

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:

[vba]
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

[/vba]

[randolphoral]

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.