Solved: Change text to numbers for different sheets and workbooks

[faboboren]

Hi Experts,

I want my code below does the same in 45 sheets and 11 workbooks. (change text to numbers in a variable range)



[VBA]Dim rrGlobal As Range
Sub ChangeUPCtonumbers()
Set rrGlobal = Range("A:A")
Call ChangeUPCtonumbers1
End Sub


Sub ChangeUPCtonumbers1()

Dim r As Range

Count = 0
For Each r In rrGlobal
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub[/VBA]

[Bob Phillips]

[vba]

Dim rrGlobal As Range
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
Sub ChangeUPCtonumbers()
Set rrGlobal = sh.Range("A:A")
Call ChangeUPCtonumbers1
Next sh
End Sub


Sub ChangeUPCtonumbers1()
Dim r As Range

Count = 0
For Each r In rrGlobal
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub
[/vba]

[faboboren]

Hi xld,

Thank you very much for your answer.

It is working perfect in all the sheets for 1 woorkbook, but it is not doing anything in the other opened woorkbooks.

Thanks

[Bob Phillips]

[vba]
Dim rrGlobal As Range
Dim sh As Worksheet
For each wb In Workbooks
For Each sh In wb.Worksheets
Sub ChangeUPCtonumbers()
Set rrGlobal = sh.Range("A:A")
Call ChangeUPCtonumbers1
Next sh
Next wb
End Sub
[/vba]


__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

[faboboren]

Hi xld,

Thanks, now it is working perfect!!

[Bob Phillips]

Please mark the thread as solved then.


__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

[Zack Barresse]

You do realize that if you loop through all open workbooks you'll loop through all hidden instances of workbooks as well, i.e. Personal.xls.

HTH