Hi,
I have a code in software that exports results to excel. But it stores the numbers associated with indices as text. I need to change all of them to numbers.
So I have written the following code, that search through the folder, converts text to numbers and colses the file.
Sub ConvertText2NumberFiles()
Dim v As Variant
Dim rng1 As Range, bk As Workbook
Dim i As Long
ChDrive "C"
ChDir "C:\ ...\analysis"
v = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xlsx),*.xlsx", _
Title:="Select Multiple Files", _
MultiSelect:=True)
If IsArray(v) Then
For i = LBound(v) To UBound(v)
Set bk = Workbooks.Open(v(i))
bk.ActiveSheet.Range("a1:t999").Select
For Each xCell In Selection
If xCell.Value = "Undef" Then xCell.Value = 0
xCell.Value = CDec(xCell.Value)
If xCell.Value = 0 Then xCell.Value = Null
Next xCell
bk.Close SaveChanges:=True
Next
Else
MsgBox "No files to process"
End If
End Sub
The problem is it takes time becuase it is going through A LOT OF cells. I only need at most the first 4 columns and the first 2 rows.
So I wanted to use the union. I wrote the following:
Set IndexColumns = bk.ActiveSheet.Range(Columns("A") & ":" & Columns("A").SpecialCells(xlLastCell))
Set IndexRows = bk.ActiveSheet.Range(Rows("1") & ":" & Rows("1").SpecialCells(xlLastCell))
Application.Union(Range("IndexColumns"), Range("IndexRows")).Select
Which doesn't work! Morover it is only for one column and one row.
NOTE1 : Like any matrix, the intersection of index columns and index rows is blank!
NOTE2 : The lenght of indices is different from one file to another
can anybody help me with this problem?
Thanks
Bookmarks