Concatenate and Autofill

[djsmarties]

Hi all,

I am running into problems with the concatenate/ filling funtion in my macro.

I am combining two cells and then the formula should autofill until my last row. I am using this succesfully with other formulas, but concatenate does not seem to want to do it.
Getting error message: "Autofill method of range class failed"
Am I missing something really obvious?

Thanks a lot

****************

Range("AT2").Select
Selection.AutoFill Destination:=Range("AT2:AT" & Cells(Rows.Count, 1).End(xlUp).Row), Type:=xlFillDefault
Range("AT7").Select

**********************

 

[northwolves]

Range("AT2").Select 
Selection.AutoFill Destination:=Range("AT2:AT" & activesheet.Cells(Rows.Count, 1).End(xlUp).Row), Type:=xlFillDefault 
Range("AT7").Select

cells is an object of worksheet.

Hope it helps.

 

[northwolves]

Range("AT2").Select 
Selection.AutoFill Destination:=Range("AT2:AT" & activesheet.Cells(Rows.Count, 1).End(xlUp).Row), Type:=xlFillDefault 
Range("AT7").Select

cells is an object of worksheet.

Hope it helps.

 

[djsmarties]

Thanks, I tried and changed my code to this, but i still get the autofill range error as before...


Range("A2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],RC[16])"

Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row), Type:=xlFillDefault
Range("A7").Select


Any ideas?
Thanks

 

[Norie]

Have you tried not using autofill?

Range("A2:A" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=RC[1] &RC[16]"

 

[djsmarties]

Thanks, I think we are getting a bit closer to a solution. I tried this:


Range("A2:A" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=RC[1] &RC[16]"


Now I am not getting any error messages, but the formula is only inserted in cell A2, not in all cells in column A...

Thanks in advance for some more ideas

 

[Norie]

Perhaps you should be looking for the last row in another column?

 

[djsmarties]

Thanks a lot for that hint . I cannot believe I didnt spot that myself

Works fine now with

Range("A2:A" & ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = "=RC[1] &RC[16]"

 

[northwolves]

[A2].Resize([B65536].End(xlUp).Row - 1, 1) = "=RC[1] &RC[16]"

Best Regards