Solved: Array Formula not recognizing Imported Data as a Number

[JimS]

Attached is an example of a data file that gets imported from a database.

For some reason some numbers are not being recognized by an Array Formula – the same numbers are recognized by a regular formula (non-array type).

If I go in and select each and every cell (1 at a time) and then hit enter then the Array Formula will count the value.

I have tried changing the Cells Format but nothing works (except selecting each cell & hitting Enter). I’ve even tried copying the data to a new workbook and pasting just the values, but that didn’t help either.

Any ideas on what causes this or how to fix it?

I cannot answer how the data gets imported because it’s from a 3rd party company that has their own Plug-in (Salesforce.com).

Thanks for any ideas…

JimS

[Kenneth Hobs]

Sub SetDataCostAsValue()
With Worksheets("Data").Range("B2", Range("B" & Rows.Count).End(xlUp))
.Value = .Value
End With
End Sub

[JimS]

Received an "Application-defined or object-defined error" on the "With Worksheets" line.

[Kenneth Hobs]

Sub SetDataCostAsValue()
With Worksheets("Data").Range("B2", Worksheets("Data").Range("B" & Rows.Count).End(xlUp))
.Value = .Value
End With
End Sub