VBA to convert to number

steelervince

Board Regular
Joined
May 29, 2007
Messages
83
I have a string of imported data which comes into an existing spreadsheet. It cannot be changed prior to importing as the data is provided by an external vendor.

When the data is imported into my spreadsheet the Error Checking tag comes on, after I select the cells where certain numeric data has been entered, stating that this is a range of Numbers Stored as Text. If I click on the Error Checking tag and select the Convert to Number option and then enter my VLOOKUP function I get the desired answer. If I do not convert the text to numbers then my VLOOKUP function does not work at all. The cell just displays the function text itself instead of the desired answer.

Here is what I am requesting:

Assistance with writing the VBA code to accomplish the manual task of converting the text to numbers. I have tried to format using the Ribbon without success.

Any ideas out there?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Try

Code:
Sub nfmt()
With ActiveSheet.UsedRange
    .Value = .Value
End With
End Sub
 
Upvote 0
Perhaps
Code:
Option Explicit
Sub Treate()
Dim I As Object
    Application.ScreenUpdating = False
    For Each I In ActiveSheet.UsedRange
        I = I.Value
    Next I
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Neither VBA suggestion has helped. The selected range still has the tag which says Number Stored as Text and I have to manually slect the option to Convert to Number.

I copied and pasted the code exactly as you have written it.
 
Upvote 0
Another possibility

Code:
Sub nfmt()
With ActiveSheet.UsedRange
    .Value = Evaluate(.Address & "*1")
End With
End Sub
 
Upvote 0
You can try just using text to columns, select delimited and go. That should convert the text numbers to real numbers.
 
Upvote 0
Is the format for all cells "general" , NOT "Text"
 
Upvote 0
If the first that I posted didn't work and the second did then I'm surprised - but as long as it works...

PCL -ce qui vous pensent? (apologies - internet translation, basically 'what do you think'?). I value your opinion.
 
Upvote 0
I 'm somehow a bit surprised about the difference between code result,in the other hand the last code could have some limits when cell value is NOT a number; but the result is there..!
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top