Macro Error "Object doesn't support this property or method: 'XLDoc.Sheets(...).Selection'"

[#1]

Hello,

I have a macro to export and format a chart to Excel (2010). The problem is Excel interprets one of the column headers as a date and changes it from 10-20 to Oct-20.

My solution was to change the cell format to text and add the column header back in but I'm getting the following error message when running the macro:

          "Object doesn't support this property or method: 'XLDoc.Sheets(...).Selection'"

               XLDoc.Sheets("Sheet4").Range("L1").Select
               XLDoc.Sheets("Sheet4").Selection.NumberFormat = "@"
               XLDoc.Sheets("Sheet4").ActiveCell.FormulaR1C1 = "10-20"

Any suggestions are greatly appreciated!

Thank you,

Sarah

[m_woolf · Accepted solution]

Try this:

    Set objCell = XLDoc.Sheets("Sheet1").Range("L1")

    objCell.NumberFormat = "@"

    objCell.FormulaR1C1 = "10-20"

[#3]

Great suggestion, it works perfectly.

Why does this work or why is it necessary to set up the variable for it to work?

Thank you,

Sarah