MSG Box to define range of Convert to Number

rsyme · Jul 28th 2022

Hello

I am attempting to create a range defined by a value input into a message box to convert a string of "General" cells to "Number" cells.

Currently I have this:

Code

Sub ConvertTextToNumber()
Set Rng = Application.Selection
Title = "MSG Box"
Set Rng = Application.InputBox("Input Range", Title, Rng.Address, Type:=8)
Do
Set cell = Rng.Find("General", LookIn:=xlValues)
If Not cell Is Nothing Then
cell.Values
End If
Loop While Not cell Is Nothing


End Sub

Display More

Which does not seem to work. Any ideas? Ultimately I want the range input into the message box (i.e. C2:D6) to be converted to number formatting.

gijsmo · Jul 29th 2022

Maybe try this:

Code

Sub ConvertTextToNumber()
  Dim Rng As Range
  Dim Title As String
  
  'set Rng to nothing initially to trap Cancel out of Msgbox
  Set Rng = Nothing

  Title = "MSG Box"
  
  'error handling if Msgbox is cancelled
  On Error Resume Next
  Set Rng = Application.InputBox("Input Range", Title, Type:=8)
  If Not Err.Number Then
    Rng.Value = Rng.Value
  Else
    Err.Clear
    On Error GoTo 0
  End If
End Sub

Display More

rsyme · Jul 29th 2022

Thank you! That worked like a charm

gijsmo · Jul 30th 2022

You're welcome!

Jimmywick · Aug 6th 2022

Code

Sub Using_Variables()


Dim valueOne As String
valueOne = 5
MsgBox CLng(valueOne) + CLng(valueOne)


End Sub

**alansidman** · Aug 6th 2022

Jimmywick

Please read the forum rules you agreed to when you joined and abide by them in the future. In this case, I have added code tags for your post because you are new to this site.

Alan

**pike** · Aug 7th 2022

possibly

Code

Option Explicit
Sub ConvertTextToNumber()
    Const Title As String = "MSG Box"
    'error handling if Msgbox is cancelled or Esc
    On Error GoTo ErrorHandler:
    With Application.InputBox("Input Range", Title, Type:=8)
        .NumberFormat = "0.00"
    End With
    Exit Sub
ErrorHandler:
End Sub

Display More

**pike** · Aug 9th 2022

Hi rsyme,

Code

.Value=.Value

wont convert the cell to number formatting.

you will need to use

Code

 .NumberFormat = "0.00"

MSG Box to define range of Convert to Number

Participate now!

Share

Similar Threads

Looping trough all workbooks in a folder an their worksheet. Action is to paste special, so that there is no formula

Count Pivot Tables and add a table

Show Age and Age Range from Date of Birth entered into userform

Copy sections (columns) of Filtered Table data from active workbook and paste into another workbook

Counting quantity - data contained in one cell by new line

Update all values in column "E" between two given ID value.