Autofill formula to the right

[Bering]

Hello,

I am sure you guys can help me with this:

my macro inserts a formula in cell D37 (FormulaCell variable) and I need to autofill this formula until the last not blank column, which in this case happens to be column M (Lastcolumn variable). I tried different solutions but no one worked, below is my last attempt, which does nothing at all...

Lastcolumn = AuditCell.CurrentRegion.Columns.Count

Set rngX1 = .Find("Accrual", LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)

Set FormulaCell = rngX1.End(xlDown).Offset(0, 3)

FormulaCell.Formula = "=" & AuditCell.Offset(1, 0).Address(1, 0) & "/" & 4

FormulaCell.AutoFill Destination:=FormulaCell.Resize(0, Lastcolumn), Type:=xlFillDefault

Thank you in advance

 

[MARK858]

Try

Formulacell.AutoFill Destination:=Formulacell.Resize(, LastColumn - (Formulacell.Column - 1)), Type:=xlFillDefault

Btw, you can't Resize by 0.

And

.CurrentRegion.Columns.Count

is likely to only find the correct column if your currentregion starts in Column A.

Better to something like

LastColumn = Cells(1, Columns.count).End(xlToLeft).Column

or

LastColumn = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

The line I posted to try relies on the LastColumn being defined by one of my 2 last suggestions

 

[Bering]

Try

Formulacell.AutoFill Destination:=Formulacell.Resize(, LastColumn - (Formulacell.Column - 1)), Type:=xlFillDefault

Btw, you can't Resize by 0.

And

.CurrentRegion.Columns.Count

is likely to only find the correct column if your currentregion starts in Column A.

Better to something like

LastColumn = Cells(1, Columns.count).End(xlToLeft).Column

or

LastColumn = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

The line I posted to try relies on the LastColumn being defined by one of my 2 last suggestions

Thank you so muck Mark,

it worked

FormulaCell.AutoFill Destination:=FormulaCell.Resize(1, Lastcolumn - (FormulaCell.Column - 1)), Type:=xlFillDefault

Just one thing, for some reason, the code below gives me 20, which is the total number of columns from A to T.

LastColumn = Cells(1, Columns.count).End(xlToLeft).Column

However, what I need is the number of column from A to L in the row defined by AuditCell.row (M, N and O are blank) so I have changed the above as:

Lastcolumn = y.Sheets("Sheet1").Cells(AuditCell.Row, Columns.Count).End(xlToLeft).Column - 8

Not very elegant but it works, maybe you could help me to fix that.

 

[MARK858]

Just one thing, for some reason, the code below gives me 20, which is the total number of columns from A to T.

If that is giving you 20 then there is something in T1, change the 1 to AuditCell.Row if you have headers that don't match the AuditCell row columns...

LastColumn =  y.Sheets("Sheet1").Cells(AuditCell.Row, Columns.count).End(xlToLeft).Column

but you have the below

Lastcolumn = y.Sheets("Sheet1").Cells(AuditCell.Row, Columns.Count).End(xlToLeft).Column - 8

What is in the cells from L to T for you to need to use the -8 on the AuditCell row? do you have formulas returning ""?

 

[Bering]

If that is giving you 20 then there is something in T1, change the 1 to AuditCell.Row if you have headers that don't match the AudditCell row columns...

LastColumn = Cells( AuditCell.Row, Columns.count).End(xlToLeft).Column

If that is giving you 20 then there is something in T1, change the 1 to AuditCell.Row if you have headers that don't match the AuditCell row columns...

LastColumn =  y.Sheets("Sheet1").Cells(AuditCell.Row, Columns.count).End(xlToLeft).Column

but you have the below

Lastcolumn = y.Sheets("Sheet1").Cells(AuditCell.Row, Columns.Count).End(xlToLeft).Column - 8

What is in the cells from L to T for you to need to use the -8 on the AuditCell row? do you have formulas returning ""?

Hello Mark,

please find below an image showing the columns in my file. The AuditCell is cell D18, columns M,N and O are blank. I need my formula to be dragged up to column L.

I hope this helps.

Many thanks

 

[MARK858]

Try...

Formulacell.AutoFill Destination:=Formulacell.Resize(, Range(Auditcell, Auditcell.End(xlToRight)).Columns.Count), Type:=xlFillDefault

or

LastColumn = Range(Auditcell, Auditcell.End(xlToRight)).Columns.Count
Formulacell.AutoFill Destination:=Formulacell.Resize(, LastColumn), Type:=xlFillDefault

 

[Bering]

Try...

Formulacell.AutoFill Destination:=Formulacell.Resize(, Range(Auditcell, Auditcell.End(xlToRight)).Columns.Count), Type:=xlFillDefault

or

LastColumn = Range(Auditcell, Auditcell.End(xlToRight)).Columns.Count
Formulacell.AutoFill Destination:=Formulacell.Resize(, LastColumn), Type:=xlFillDefault

Works like a charm!!!! thanks a lot for your help, much appreciated

 

[MARK858]

You're welcome