Messed up code :-/

**james 35** · 03-07-2013, 05:25 AM

Below is a code I've tried to alter the change values in certain cells in sheets with the same prefix to proper case ...

Can anyone advise where I've gone wrong ??

Thanks

James

Public Sub Proper_Case()
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
 
Set rng = ActiveWorkbook.Sheets("Control").Range(ws.Name Like "PP*")
For Each SH In ActiveWorkbook.Worksheets
If Not IsError(Application.Match(SH.Name, rng, 0)) Then
Range("B5").Select
ActiveCell = WorksheetFunction.Proper(ActiveCell.Value)
Range("I5").Select
ActiveCell = WorksheetFunction.Proper(ActiveCell.Value)
End If
Next SH
End Sub

**AB33** · 03-07-2013, 05:31 AM

Did you get an error, or the code does not work?

**james 35** · 03-07-2013, 05:46 AM

Yes the set rng line is where the debugger points to ?

james

**james 35** · 03-07-2013, 05:53 AM

just got this to work but it doesnt loop through all sheets ?

Public Sub Proper_Case()
Dim SH As Worksheet

For Each SH In ActiveWorkbook.Worksheets
If SH.Name Like "PP*" Then
Range("B5").Select
ActiveCell = WorksheetFunction.Proper(ActiveCell.Value)
Range("I5").Select
ActiveCell = WorksheetFunction.Proper(ActiveCell.Value)
End If
Next SH
End Sub

**Norie** · 03-07-2013, 06:02 AM

James

Try this.

Public Sub Proper_Case()
Dim SH As Worksheet

   For Each SH In ActiveWorkbook.Worksheets
      If SH.Name Like "PP*" Then
         SH.Range("B5").Value =
WorksheetFunction.Proper(SH.Range("B5").Value)
         SH.Range("I5").Value =
WorksheetFunction.Proper(SH.Range("I5").Value)
      End If
   Next SH
End Sub

**james 35** · 03-07-2013, 06:10 AM

That worked a treat Norie :-))))))))

Thank you

James

Originally Posted by Norie

James

Try this.

Public Sub Proper_Case()
Dim SH As Worksheet

   For Each SH In ActiveWorkbook.Worksheets
      If SH.Name Like "PP*" Then
         SH.Range("B5").Value =
WorksheetFunction.Proper(SH.Range("B5").Value)
         SH.Range("I5").Value =
WorksheetFunction.Proper(SH.Range("I5").Value)
      End If
   Next SH
End Sub