Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select Range and Convert Text to Numbers in Excel VBA

Posted on 2013-01-22
2
Medium Priority
?
460 Views
Last Modified: 2013-01-23
1) I need to select cells from H3 to Mxxx where total rows are variable and unknown

2) I need to convert those values from Text to Number

I found this much code and it works for any selection that I manually highlight.  But I'm having trouble with number #1.

    '   Check to see if selection is formatted as Text -
    '   If so, then reformat selection as General
    '**********************************************************
    If Selection.NumberFormat = "@" Then _
            Selection.NumberFormat = "General"
           
    '   Replace formulas and numeric strings with real numbers
    '**********************************************************
    Selection.Value = Selection.Value
0
Comment
Question by:newknew
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 38807901
To apply to the last populated cell in Col M...


Dim LastR As Long

With ActiveSheet
    LastR = .Cells(.Rows.Count, "m").End(xlUp).Row
    With .Range("h3:m" & LastR)
        .NumberFormat = General
        .Value = .Value
    End With
End With

Open in new window

0
 
LVL 1

Author Closing Comment

by:newknew
ID: 38810763
worked perfectly, thank you!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question