Convert range format to number

kevinbradley57 · #1 07-26-2018, 08:42 PM

I need the right syntax to convert the format of a range to "number". Here is my attempt:

Code:

Range ["K2:N" & lastrow].NumberFormat = "0.00"

Kenneth Hobson · #2 07-26-2018, 09:19 PM

Change []'s to ()'s.

kevinbradley57 · #3 07-27-2018, 06:33 PM

I fixed the brackets. Now I get a Run-time error: Method 'Range' of object '_Global' failed.

Code:

 
Sub GetInProgress()
   Dim wb As Workbook, src As Workbook
   Dim sht As Worksheet, cel As Range
   Dim LR As Long
   
Set sht = ThisWorkbook.ActiveSheet
'remove existing data
With sht.UsedRange.Offset(1)
   .Borders.LineStyle = xlNone
   .ClearContents
End With
'see if source is open
For Each wb In Application.Workbooks
   If wb.Name = "SearchResultsInProgress " & Format(Date, "m.d.yy") & ".xls" Then
       Set src = wb
       Exit For
   End If
Next wb
'if yes copy data
If Not src Is Nothing Then
   src.Sheets(1).UsedRange.Offset(1).Copy
'if no display message and quit
Else
   MsgBox "Workbook   " & Chr(34) & "SearchResultsInProgress " & _
           Format(Date, "m.d.yy") & ".xls" & Chr(34) & "   is not open."
   Exit Sub
End If
'paste the copied data
With sht
   .Cells(2, 1).PasteSpecial (xlPasteValues)
   'name sheet
'  .Name = Left(src.Name, Len(src.Name) - 4)
   ' E from F if blank
  
   For Each cel In Intersect(.Columns("E"), .UsedRange)
       If cel.Value = "" Then cel.Value = cel.Offset(, 1).Value
   Next cel
   'position the cursor for sorting
      .Cells(2, 5).Select
   'determine last row
   LR = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

   With .Sort
       .SortFields.Clear
       .SortFields.Add Key:=Range("E2"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                            DataOption:=xlSortTextAsNumbers
       .SetRange Range("A2:N" & LR)
       .Header = xlNo
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
   
Range("K2:N").Select
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With

Kenneth Hobson · #4 07-27-2018, 07:25 PM

You can use F8 to run one line of code for each press to find the problem.

You should know there is an error here:

Code:

Range("K2:N").Select

kevinbradley57 · #5 07-28-2018, 03:39 PM

Yes, I know there's an error there. That's the part of the code I had a problem with in the first place. I still don't know how to fix it.

jeffreybrown · #6 07-28-2018, 04:22 PM

You forgot the variable to establish the last row

Code:

Range("K2:N" & LastRow)

kevinbradley57 · #7 07-29-2018, 08:25 PM

What is the proper syntax to convert a cell in which a number is stored as text and a negative number is displayed in parenthesis e.g. "(145.50)" to a format in which a negative number is displayed with a negative sign prefix e.g. "-145.50"?

The code below gives me this Run-time error: Method 'Range' of object '_Global' failed.

Code:

With sht
    .Range("K2:N" & lastrow).NumberFormat = "General"
End With

Thank you.

p45cal · #8 07-30-2018, 08:34 AM

try:

Code:

With sht.Range("K2:N" & LR)
  .NumberFormat = "General"
  .Value = .Value
End With

You don't seem to have assigned anything to lastrow, but you have assigned something to LR.

kevinbradley57 · #9 07-30-2018, 03:21 PM

Perfect. Thank you, p45cal!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Format a range of cells	Dave T	Excel Programming	1	06-01-2016 08:58 PM
How to format a TOC (level 1) using a number, but have the number hidden in the text	porpoiseoil	Word	1	09-13-2014 12:53 PM
IF statement - number range	irhut	Mail Merge	1	02-11-2014 11:20 PM
Change from General Format to number format	gbaker	Excel	3	08-16-2013 01:04 PM
Cannot Convert Text Cell to number format to be able to sort the data	jyfuller	Excel	10	06-19-2013 05:31 PM