CODEWITHSUNDEEP
vbaexcelexcel-2013
RedLight GreenLight
RedLight GreenLight

Reputation: 183

Excel VBA To AutoFill Column

In Column A I have a list of 5 names - I am wanting to in column B create the different Teams and name them Team_1, Team_2 etc etc

This is the syntax I have tried...

Function AutoFill()
  Dim KCLR As Long
  KCLR = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
  Range("B2").Select
  ActiveCell.FormulaR1C1 = "Team_1"
  Selection.AutoFill Destination:=Range("B2:" & KCLR)
End Function

However, on the line Selection.AutoFill Destination:=Range("B2:" & KCLR) I get an error of

Range("B2:" & KCLR) = Method 'Range' Of Object '_Global' failed

What do I need to do in order for this to successfully AutoFill as I need?

Upvotes: 0

Views: 5663

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

Following @harun24hr answer, for future better coding practice, avoid using Select , ActiveCell and Selection, and use fully qualified Ranges.

See example below:

Function AutoFill()

  Dim KCLR As Long

  KCLR = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
  Range("B2").FormulaR1C1 = "Team_1"
  Range("B2").AutoFill Destination:=Range("B2:B" & KCLR)

End Function

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36770

Selection.AutoFill Destination:=Range("B2:" & KCLR) should be Selection.AutoFill Destination:=Range("B2:B" & KCLR) as KCLR is returning a number.

Upvotes: 2

Related Questions