PDA

View Full Version : Solved: Change text to numbers for different sheets and workbooks



faboboren
11-22-2007, 07:30 AM
Hi Experts,

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



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

Bob Phillips
11-22-2007, 07:35 AM
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

faboboren
11-22-2007, 08:43 AM
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
11-22-2007, 10:19 AM
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



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

faboboren
11-22-2007, 10:59 AM
Hi xld,

Thanks, now it is working perfect!!

Bob Phillips
11-22-2007, 11:17 AM
Please mark the thread as solved then.


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

Zack Barresse
11-22-2007, 11:27 AM
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