Working with tables to auto fill accross columns and rows.

[Dave01]

Hi,

Would really appreciate a solution to this problem, having spent hours trying to come up with one and not being able to find one.

situation: I have a and excel table, and in it contains one row of formulas, which I would like to autofill to the last column and the last row. ( so fill across and down)
I cant use range ("AD2: AS2" & lastRow, LastCol) etc because that may not be the last column in the future so I would like to count to the last column and auto fill.

As the formula position or table column where the formula is may also change, instead of using a cell ref - ie AD2, i have select the cell and am starting from active cell.

The code below does work but with two issues

It will only fill accross one line, and by a set number of columns 15

'xxxxxxxxxxxxxxxxxxxxxx
'Find column header
'xxxxxxxxxxxxxxxxxxxxxx

Range("GRIR_Table[[#Headers],[Ageing Catagory]]").Select
Cells.Find(What:="AP Researched by", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

'xxxxxxxxxxxxxxxxxxxx
'move down one cell
'xxxxxxxxxxxxxxxxxxxxxxxx

ActiveCell.Offset(1, 0).Activate

'xxxxxxxxxxxxxxxxxxxx
'enter formula
'xxxxxxxxxxxxxxxxxx

ActiveCell.Formula2R1C1 = "=IFERROR(INDEX(ImportTable[[ Researched by]],MATCH(GRIR_Table[@[PO/PO Line]:[PO/PO Line]],ImportTable[[PO/PO Line]:[PO/PO Line]],0)),"""")"

'xxxxxxxxxxxxxxxxxxx
'Fill formula
'xxxxxxxxxxxxxxx

Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(0, 15)), Type:=xlFillDefault

'*******************************************************************************************************************************

I have added the count cells / rows from active cell

and tried all the code I can find, but my only results where to apply the same formulas accross the whole cells I want to fill accross given me the exact same data as if I copied and pasted
so now I am well and truly stumped.

This is the only issue that stopping me from completing,
any help would be great

many thanks

Dave

 

[Jimmypop]

Hi there

This is untested but try the below...(replace yours with this one)

'xxxxxxxxxxxxxxxxxxx
'Fill formula
'xxxxxxxxxxxxxxx
Dim LastRow As Long
LastRow = 20 'change as necessary
Selection.AutoFill Destination:=Range(Cells(2, 15), Cells(LastRow, 15)), Type:=xlFillDefault 'The 2 and the 15 indicates row & column numbers...change as required

 

[Dave01]

Hi

I get an error with this


Run error 44

Autofill method of range class failed



i did manage to prep some test data heres the link

test Automated clearing report.xlsm

Shared with Dropbox

www.dropbox.com

What I am hoping for is to move away from fixed columns and rows, (I can do this in a non table ) but it has to be tabled as its less breakable if the columns move


I am hoping it will take the current active cell - will be AD2 in this case

count how many columns to the end of the table, & how many rows down, then autofill the lot, my previous attempts have just lead to the same formula in every cell with the same data.
in the formula the only thing that needs to change is the index reference, every thing else says the same - the matching bit

 

[Dave01]

test Automated clearing report.xlsm

Shared with Dropbox

www.dropbox.com

updated but still doesnt work

 

[Jimmypop]

Hi @Dave01

Sorry, I have been a bit busy... Try the below... When I ran it on my side I think it worked like you wanted...

Inserts a formula at AD2 and then autofill's down and right...

Sub Macro4()
    Dim GRrepLastColumn As Long, GRrepLastRow As Long
    GRrepLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    GRrepLastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Range("GRIR_Table[[#Headers],[Ageing Catagory]]").Select
    Cells.Find(What:="AP Researched by", After:=ActiveCell, LookIn:= _
                         xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                         xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Activate
    ActiveCell.Formula2R1C1 = "=IFERROR(INDEX(ImportTable[[ Researched by]],MATCH(GRIR_Table[@[PO/PO Line]:[PO/PO Line]],ImportTable[[PO/PO Line]:[PO/PO Line]],0)),"""")"
    Range(ActiveCell, Cells(GRrepLastRow, GRrepLastColumn)).FillDown
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFill Destination:=Range(ActiveCell, Cells(GRrepLastRow, GRrepLastColumn)), Type:=xlFillDefault
End Sub

Before


After running the Macro 4

 

[Dave01]

Hi,

That is perfect, thank you, I was really stuck. Works perfect.

Dave.

 

[Jimmypop]

Hi,

That is perfect, thank you, I was really stuck. Works perfect.

Dave.

No problem... Glad we could help... Lol I too had some frustrations and sleepless nights trying to figure it out