Macro to Convert Number Stored as Text to a Number

[Michael151]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> Hello all,

Just need a little help writing a macro that will convert a text to a number in a specific column.

I have this column of dates (years) such as this:



<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 48pt;" width="64" height="17">season</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">1986</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">1990</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">2005</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">1982</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">1979</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">1991</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">1990</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">2004</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">1993</td> </tr> </tbody></table>
I get the error message “number stored as text” since I’m exporting this data from an Access query. However, the column “season” is not always in column A. Sometimes in column in A, sometimes in column F or G, etc. What I would need the macro to do is find the column labeled “season” (header always in row 1) and take the text stored as a number and convert it to a number.

Any help would be most appreciated! Thanks!

 

[Jonmo1]

Try

Sub test()
Dim c As Range, lr As Long
On Error Resume Next
Set c = Rows(1).Find("Season")
On Error GoTo 0
If Not c Is Nothing Then
    lr = Cells(Rows.Count, c.Column).End(xlUp).Row
    With c.Resize(lr, 1)
        .Value = .Value
    End With
End If
End Sub

 

[Michael151]

Thanks Jonmo1!

For some strange reason, this converted all the dates to the year 1905.

 

[Jonmo1]

Format the cells as NUMBER, not Date...

Add this line

.NumberFormat = "0"

after

.Value = .Value

 

[Michael151]

Thanks Jonmo1!! That worked!