Using VB Macro to change case and font size

[conradl]

Hello all,

I am trying to come up with a macro that will do the following - look at text within a range of cells, and convert all 11pt lowercase letters of that text to 9pt uppercase, producing a small caps appearance that will mimic the appearance of Word documents that have the small caps effect applied. I have a macro that changes text to all caps and then increases the size of the first letter, but the text I want to manipulate is a mix of full sentences and proper names, so it leaves many letters that should be "capitalized" as a small cap, which I then have to correct manually. There must be a better way! Ideally this macro would also update the sheet as I make changes to the range, so that when I edit the text I don't have to run the macro again. I am using Office 2011 for Mac. Thanks

 

[eduzs]

Try this:

Sub test()

Dim aCell As Range, i As Long, temp As String, x As Long, z As Long

For Each aCell In ActiveSheet.UsedRange

With aCell

z = Len(aCell.Value)

For i = 1 To .Characters.Count

With .Characters(i, 1)

If .Font.Size = 11 Then

For x = 1 To Len(aCell.Value)

If Len(temp) < z Then temp = temp & UCase(Mid(aCell.Value, x, 1))

Next x

.Font.Size = 9

aCell.Value = temp

End If

End With



Next i


End With

Next aCell

End Sub


BACKUP YOUR ORIGINAL FILE BEFORE TESTING

 

[conradl]

educes, thanks so much for taking a crack at helping me with this. I ran it, but for whatever reason it copied some of the first couple of rows all over the sheet. The debugger is pointing at the line of code that reads For i = 1 To .Characters.Count not sure what that means if anything, or how to fix it, but of course if I did I probably wouldn't be begging for help. Thanks

PS I copied and pasted the code and then selected the entire sheet before I ran it. Is that right, or do I need to change the way I'm creating the macro and or what I select before I run it?

Try this:

Sub test()

Dim aCell As Range, i As Long, temp As String, x As Long, z As Long

For Each aCell In ActiveSheet.UsedRange

With aCell

z = Len(aCell.Value)

For i = 1 To .Characters.Count

With .Characters(i, 1)

If .Font.Size = 11 Then

For x = 1 To Len(aCell.Value)

If Len(temp) < z Then temp = temp & UCase(Mid(aCell.Value, x, 1))

Next x

.Font.Size = 9

aCell.Value = temp

End If

End With



Next i


End With

Next aCell

End Sub


BACKUP YOUR ORIGINAL FILE BEFORE TESTING

 

[eduzs]

Paste the code in a module within your workbook

 

[conradl]

I did that just now - it does the same thing, however this time I wrote down the error

It says Run-time error '1004':

Method 'Count' of object 'Characters' failed

Does that offer any clues?
<attachment></attachment>
<attachment></attachment>

Paste the code in a module within your workbook

 

[eduzs]

Sub test() 

   Dim aCell As Range, i As Long, temp As String, x As Long, z As Long
    
    For Each aCell In ActiveSheet.UsedRange

        With aCell
            
            If IsNumeric(aCell.Value) Then GoTo jump
            
            z = Len(aCell.Value)
        
            For i = 1 To .Characters.Count
        
                With .Characters(i, 1)
        
                    If .Font.Size = 11 Then
                        
                        For x = 1 To Len(aCell.Value)
                        
                            If Len(temp) < z Then temp = temp & UCase(Mid(aCell.Value, x, 1))
                        
                        Next x
                   
                       .Font.Size = 9
        
                        aCell.Value = temp
        
                    End If
        
                End With
        
        
        
            Next i
        
jump:
            
        End With

    Next aCell

End Sub

 

[conradl]

I pasted that directly into a new module and got the same result, with the same error message... What am I missing?

 

[eduzs]

Make a last try:

Below SUB put:

On error resume next