Autofit Method of Range Class Failed (Run Time Error 1004)

Question

This is just part of my code. the value from the textbox here already gets copied to the specific cell in the Bank Certification worksheet. I need to make sure that cell C5 is specifically fitted regardless of the length of the text i inputted in the textbox. I tried interchanging range with cells to no avail. This problem seems so simple but I don't know why it doesn't work...

Dim counterparty As String  
counterparty = Sheet1.txt1.Text

Range("C5").Value = counterparty 

Sheets("Bank Certification").Select

Range("C5").Select 

Selection.AutoFit

Answer 1

Try

Dim counterparty As String
counterparty = Sheet1.txt1.Text

Range("C5").Value = counterparty

Sheets("Bank Certification").Select

Columns("C:C").Autofit

Answer 2

Other answers correctly state that AutoFit must be used with a column, not just a cell. However, there are some nuances to using AutoFit that I didn't understand until I started experimenting.

Either of the first two statements below will use all values in column C to AutoFit the width of the column. That means if there is a value in some other cell in column C (for example C10) that is wider than the value in C5, it will fit the column to the widest cell in column C (for example C10).

Range("C5").EntireColumn.AutoFit     ' Will fit to widest cell in column
Range("C:C").AutoFit                 ' Will fit to widest cell in column

If you want to just fit the column on 1 cell (or a certain range of cells, but not the whole column or columns), use a statement like this:

Range("C5").Columns.AutoFit          ' Will fit column C to width of cell C5

And of course, it's always better form to write code like this when you can:

1. Fully qualify the range unless you're absolutely sure you'll only be working with one worksheet
2. Use Named Ranges or Range objects.

For example:

Workbooks("MyWorkbook.xlsm").Sheets("Sheet1").Range("MyData").Columns.AutoFit
'  or
Set AutoFitRange = Workbooks("MyWorkbook.xlsm").Sheets("Sheet1").Range("C5")
AutoFitRange.Columns.AutoFit