Compile Error

[aziz rasul]

I have the following code which I think must have worked in the past. But when I compile the code I get an error (Method or data member not found) on the line marked in red

Public Sub FormatEntireColumn(strExcelFile As String, strHeader As Boolean, strWorksheet As String, strColumnLetter As String, strDataType As String, intDecimalPlaces As Integer)
'Make sure that the passing spreadsheet has the correct extension for the MS Access version you are using.

    Dim objExcelApp As Excel.Application
    Dim ws As Excel.Worksheet
    Dim x As Integer
    Dim lngRow As Long
    Dim xCell As Range
   
    Set objExcelApp = New Excel.Application
    
    With objExcelApp
        .Workbooks.Open FileName:=strExcelFile
        .Visible = False
        For Each ws In .Worksheets
            If ws.Name = strWorksheet Then
                ws.Select
                If strHeader = True Then
                    .Range(strColumnLetter & "2:" & strColumnLetter & "65000").Select
                    lngRow = ws.UsedRange.Rows.Count
                Else
                    .Range(strColumnLetter & "1:" & strColumnLetter & "65000").Select
                    lngRow = ws.UsedRange.Rows.Count
                End If
            End If
            If ws.Name = strWorksheet Then
                If strDataType = "Date" Then
                    .Selection.NumberFormat = "m/d/yyyy"
                    .Range("A1").Select
                    Exit For
                ElseIf strDataType = "Number" Then
                    .Columns(strColumnLetter & ":" & strColumnLetter).Select
                    If intDecimalPlaces = 0 Then
                        .Selection.NumberFormat = "0"
                    ElseIf intDecimalPlaces = 1 Then
                        .Selection.NumberFormat = "0.0"
                    ElseIf intDecimalPlaces = 2 Then
                        .Selection.NumberFormat = "0.00"
                    ElseIf intDecimalPlaces = 3 Then
                        .Selection.NumberFormat = "0.000"
                    ElseIf intDecimalPlaces = 4 Then
                        .Selection.NumberFormat = "0.0000"
                    End If
                    If strHeader = True Then
                        .Range(strColumnLetter & "2").Select
                    Else
                        .Range(strColumnLetter & "1").Select
                    End If
                    .Range(.Selection, .Selection.End(xlDown)).Select
                    For Each xCell In .Selection
                       [COLOR="Red"]xCell.Value = xCell.Value[/COLOR]
                    Next xCell
                    .Range("A1").Select
                    .Range(strColumnLetter & "1").Select
                    .Range(.Selection, .Selection.End(xlDown)).Select
                    .Range(strColumnLetter & "2:" & strColumnLetter & lngRow).Select
                    .Range(strColumnLetter & lngRow + 1).Activate
                    .ActiveCell.FormulaR1C1 = "=SUM(R[-" & lngRow - 1 & "]C:R[-1]C)"
                    .Selection.Font.Bold = True
                    .Selection.NumberFormat = "$#,##0"
                    .Selection.Cut
                    .Range(strColumnLetter & lngRow + 2).Select
                    .ActiveSheet.Paste
                    .Range("A1").Select
                    Exit For
                ElseIf strDataType = "Text" Then
                    .Columns(strColumnLetter & ":" & strColumnLetter).Select
                    .Selection.NumberFormat = "@"
                End If
            End If
        Next
        .DisplayAlerts = False
        .ActiveWorkbook.SaveAs strExcelFile
        .Quit
        .DisplayAlerts = True
    End With
    
    Set objExcelApp = Nothing

End Sub

 

[mdlueck]

What would the useful purpose of this bit even be...

For Each xCell In .Selection
  [COLOR=Red]xCell.Value = xCell.Value[/COLOR]
Next xCell

It looks like it is setting each cell's value to the value of self. "And the point of that would be????"

 

[aziz rasul]

Michael, I wish I knew. I was racking my brains thinking this must have worked in the past as I have had this code for a while and it was only when I was compiling that the issue came up. I agree it doesn't make much sense.

I will try running the code without the bit in question and see if it works. That might give me a clue.