Run Time error 1004

[schniggeldorf]

Hi:

I am trying to write vba code that checks each used cell in a specific worksheet and changes its format to numeric, with zero decimal places. However, I want the code to skip the contents of 3 columns, as these contain dates or strings.

My code is as follows:

With ThisWorkbook.Worksheets("KpData")
        For Each cell In .UsedRange            If cell.Row <> 1 Then
                Debug.Print "Before: " & cell.Address, cell.NUMBERFORMAT
                ColLetter = ConvertToLetter(cell.Column)
                HeaderValue = Range(ColLetter & "1").Value
                If cell.Value = "NULL" Then
                    cell.ClearContents
                    cell.ClearFormats
                ElseIf HeaderValue <> "EncounterDate" And HeaderValue <> "ADM_Date" And HeaderValue <> "Prefix" Then
                    cell.NUMBERFORMAT = "0" 'This converts it to a number format.
                End If
            End If
            Debug.Print "After: " & cell.Address, cell.NUMBERFORMAT
        Next cell
End With

The code runs fine, until it gets to cell BA2, when it gives me a run time error 1004. I've tried using it to process several different spreadsheets, and regardless of what data are in the worksheets, it always bails at BA2. (The last used column in each worksheet is EX.)

I've searched the web for this error, but nobody else's situation seems parallel to mine.

Can anybody offer ideas? Thanks.

 

[JoeMo]

Post the function:

ConvertToLetter

 

[schniggeldorf]

Here it is:

Public Function ConvertToLetter(iCol As Integer) As StringDim iAlpha As Integer
Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function