Autofill formula to the right

Bering

Board Regular
Joined
Aug 22, 2018
Messages
134
Office Version
  1. 2016
Platform
  1. Windows
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...

VBA Code:
Lastcolumn = AuditCell.CurrentRegion.Columns.Count
VBA Code:
Set rngX1 = .Find("Accrual", LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
VBA Code:
Set FormulaCell = rngX1.End(xlDown).Offset(0, 3)
VBA Code:
FormulaCell.Formula = "=" & AuditCell.Offset(1, 0).Address(1, 0) & "/" & 4
VBA Code:
FormulaCell.AutoFill Destination:=FormulaCell.Resize(0, Lastcolumn), Type:=xlFillDefault

Thank you in advance
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,034
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try
VBA Code:
Formulacell.AutoFill Destination:=Formulacell.Resize(, LastColumn - (Formulacell.Column - 1)), Type:=xlFillDefault

Btw, you can't Resize by 0.

And
VBA Code:
.CurrentRegion.Columns.Count
is likely to only find the correct column if your currentregion starts in Column A.

Better to something like
VBA Code:
LastColumn = Cells(1, Columns.count).End(xlToLeft).Column
or
VBA Code:
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
 
Last edited:

Bering

Board Regular
Joined
Aug 22, 2018
Messages
134
Office Version
  1. 2016
Platform
  1. Windows
Try
VBA Code:
Formulacell.AutoFill Destination:=Formulacell.Resize(, LastColumn - (Formulacell.Column - 1)), Type:=xlFillDefault

Btw, you can't Resize by 0.

And
VBA Code:
.CurrentRegion.Columns.Count
is likely to only find the correct column if your currentregion starts in Column A.

Better to something like
VBA Code:
LastColumn = Cells(1, Columns.count).End(xlToLeft).Column
or
VBA Code:
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 (y)

VBA Code:
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.


VBA Code:
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:

VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,034
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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...
VBA Code:
LastColumn =  y.Sheets("Sheet1").Cells(AuditCell.Row, Columns.count).End(xlToLeft).Column
but you have the below
VBA Code:
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 ""?
 
Last edited:

Bering

Board Regular
Joined
Aug 22, 2018
Messages
134
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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...
VBA Code:
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...
VBA Code:
LastColumn =  y.Sheets("Sheet1").Cells(AuditCell.Row, Columns.count).End(xlToLeft).Column
but you have the below
VBA Code:
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.

Capture.PNG


I hope this helps.

Many thanks
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,034
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try...
VBA Code:
Formulacell.AutoFill Destination:=Formulacell.Resize(, Range(Auditcell, Auditcell.End(xlToRight)).Columns.Count), Type:=xlFillDefault
or
VBA Code:
LastColumn = Range(Auditcell, Auditcell.End(xlToRight)).Columns.Count
Formulacell.AutoFill Destination:=Formulacell.Resize(, LastColumn), Type:=xlFillDefault
 
Solution

Bering

Board Regular
Joined
Aug 22, 2018
Messages
134
Office Version
  1. 2016
Platform
  1. Windows
Try...
VBA Code:
Formulacell.AutoFill Destination:=Formulacell.Resize(, Range(Auditcell, Auditcell.End(xlToRight)).Columns.Count), Type:=xlFillDefault
or
VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,034
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,129,284
Messages
5,635,305
Members
416,851
Latest member
zeldadav

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top