0

This should capitalize every character but I get type mismatch error. It works fine for other worksheets that have similar data but for no reason it gives me mismatch error. Please help 

Private Sub allUpper(ByRef sh As Worksheet)
        Dim arr As Variant, i As Long, j As Long

        If WorksheetFunction.CountA(sh.UsedRange) > 0 Then
            arr = sh.UsedRange  'one interaction with the sheet
            For i = 2 To UBound(arr, 1)         'each "row"
                For j = 1 To UBound(arr, 2)     'each "col"
                    arr(i, j) = UCase(RTrim(Replace(arr(i, j), Chr(10), vbNullString)))
                Next
            Next
            sh.UsedRange = arr  'second interaction with the sheet
        End If
    End Sub
Erik A
  • 31,639
  • 12
  • 42
  • 67
BLkrn
  • 77
  • 2
  • 14
  • 1
    Why Loop? See [THIS](http://stackoverflow.com/questions/19985895/convert-an-entire-range-to-uppercase-without-looping-through-all-the-cells) – Siddharth Rout Jun 16 '15 at 20:04

1 Answers1

0

You probably have an error (#N/A, etc.) somewhere in your data.

You can add a check for that to prevent the run time error:

If Not IsError(arr(i, j)) Then
    arr(i, j) = UCase(RTrim(Replace(arr(i, j), Chr(10), vbNullString)))
End If
Byron Wall
  • 3,970
  • 2
  • 13
  • 29