VBA To Convert Text To Numbers

[passmaster16]

Hi,

I pull data into Excel 2007 from a SharePoint query. The data is pulled over into Excel in the form of a table. I would like to convert the data in columns C through I from text to numeric so that I can use the AVERAGE forumula to calculate averages for each row. I would like to be able to do this through VBA as I will also automate adding and population of the averaged column. The only problem I see is that this data is dynamic. Each month I clear the spreadsheet and run the query to grab the latest data so I need to be able to select the data from a dynamic range and convert it to numbers. Any ideas on how I can accomplish this with VBA?

Thanks in advance!

 

[MrKowz]

Try using Text-To-Columns on those columns. Highlight a column, Data>Text to Columns, then click Finish.

 

[jmthompson]

Find a column that will always be populated, no blanks, until the end of your data, then update the lastRow line in this code with your column. I've used column A here. I also assumed you have a header in row 1, if not, update the For each range to C1:I...

Sub Convert()
Dim c As Range
Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("C2:I" & lastRow)
    If c <> "" Then
    c = 1 * c.Value
    End If
Next c

End Sub