0

Trying to execute below code:

ActiveCell.Offset(1, 0).Select 'Noting but F2





  ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"





' find last row with data in column "A"

lastRow = Cells(Rows.Count, "A").End(xlUp).Row



' copying the formula from "F2" all the way to the last cell

ActiveCell.AutoFill Destination:=Range(ActiveCell.EntireColumn & lastRow), Type:=xlFillDefault

It gives an error saying type mismatch.

But when I try:

ActiveCell.AutoFill Destination:=Range("F2:F" & lastRow), Type:=xlFillDefault

It works properly, but I don't want F2:F there, as I don't know which column will be in future.

paran
  • 199
  • 1
  • 3
  • 18
  • You should read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Apr 29 '20 at 09:41
  • `Range(ActiveCell.EntireColumn & lastRow)` makes no sense. – SJR Apr 29 '20 at 09:41
  • Hmm, what can we use instead of that or "F2:F" as the column number will be unknown at time of real execution. Only thing will be same i.e Header (Value in Row 1). @SJR – paran Apr 29 '20 at 09:59
  • use `cells(2,activecell.column)` and `cells(lastrow,activecell.column)`. The active cell must be in the destination range. – SJR Apr 29 '20 at 10:03
  • 1
    That said, you can just apply the formula in one go, no need for autofill. – SJR Apr 29 '20 at 10:03
  • You've been advised against Selects before and given that link - if you're going to post here please listen to people's advice. – SJR Apr 29 '20 at 10:05

1 Answers1

1

To answer your question you are not specifying a right range. A simple range can be, as you said, specified

  1. Using the string format Range("A1:B4") where A1 and B4 are two opposite corners of the range.

  2. Using the two corners as cells, e.g. Range( Cells(1,1), Cells(4,2) ) where Cells(1,1) is A1 and Cells(4,2) is B4

So you can write

ActiveCell.AutoFill Destination:=Range(Cells(2, ActiveCell.Column), Cells(lastRow, ActiveCell.Column)), Type:=xlFillDefault

To reach your goal you could also avoid using the AutoFill. FormulaR1C1 format allow you to write formula with 'relative' cell offsets/address. So after the AutoFill all the cells in the desiderd column will have the same FormulaR1C1. Then you can avoid using the AutoFill and manually set the same FormulaR1C1 for the whole column.

So you can write a simpler code like that:

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(1, ActiveCell.Column), Cells(lastRow, ActiveCell.Column)).FormulaR1C1 = "=LEFT(RC[-1],4)"
Igor
  • 157
  • 7