Formula global failure to copy range format down to final row

J

Jacko1307

Board Regular
Joined
Sep 4, 2012
Messages
92
Office Version
  1. 2016
Platform
  1. Windows
Good morning,

Having a slight problem making excel copy a formula from F2 to FinalRow and keep getting global failure. Code I have so far.
Please can you help me with this conundrum.

Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F1").Select
ActiveCell.FormulaR1C1 = "AVG"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]" (NOT SURE IF THIS IS PROBLEM)
Selection.NumberFormat = "0.00"
Selection.AutoFill Destination:=Range("F2:F" & FinalRow), Type:=xlFillDefault
Range("F2:F").Select
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
When posting code, please use code tags. My signature block below has details.

You have not shown us how 'FinalRow' is declared or given a value. that error could occur if FinalRow does not have a value.
If you run the code again and Debug on the error then hover over FinalRow in the code, what value appears in the pop-up?
Could it be like this?
1604832911160.png


I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Final Row is declared as shown below

Sub FinalRow()

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

There are other steps above the lines of code I posted but it is the selection of F2 which contains =E2/D2 with format number 0.00

It then sends it into the R1C1 reference which I need to be dynamic as the number of rows varies daily.
 
Upvote 0
VBA Code: 
Sub Jacko1307()
'
' Jacko1307 Macro

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
 
'
Application.ScreenUpdating = False
    Columns("F" & FinalRow).Select
    Selection.ClearContents
    Columns("F" & FinalRow).Select
    Selection.ColumnWidth = 25
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "EXPORT"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "FARM"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "CONTRACT"
    Range("A1:H" & FinalRow).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("1" & FinalRow).Select
    Selection.RowHeight = 20
    Range("D" & FinalRow + 1).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    Range("E" & FinalRow + 1).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    Range("A1:H" & FinalRow).Select
    Range("H1").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "AVG"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
    Selection.NumberFormat = "0.00"
    Selection.AutoFill Destination:=Range("F2:F" & FinalRow), Type:=xlFillDefault
    Range("F2:F").Select
Application.ScreenUpdating = True

    Columns("A:B").EntireColumn.AutoFit
End Sub
 
Upvote 0
I would expect your code to fail right at the start on this line
VBA Code: 
Columns("F" & FinalRow).Select
as that is not valid code.
What are you trying to select?
 
Upvote 0
Fluff said:
I would expect your code to fail right at the start on this line
VBA Code: 
Columns("F" & FinalRow).Select
as that is not valid code.
What are you trying to select?
Click to expand...
I pull a CSV file using HCS Exporter on our system this gives us a File which is the total daily products and grades.

pre macro.JPG
Post Macro.JPG
 
Last edited by a moderator:
Upvote 0
VBA Code: 
Sub Jacko1307()
'
' Jacko1307 Macro

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
 
'
Application.ScreenUpdating = False

    Columns("F:F").Select
    Selection.ClearContents
    Columns("F:H").Select
    Selection.ColumnWidth = 25
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "EXPORT"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "FARM"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "CONTRACT"
    Range("A1:H" & FinalRow).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("D" & FinalRow + 1).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    Range("E" & FinalRow + 1).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
    Range("A1:H" & FinalRow).Select
    Range("H1").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "AVG"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
    Selection.NumberFormat = "0.00"
    Selection.AutoFill Destination:=Range("F2:F" & FinalRow), Type:=xlFillDefault
    Range("F2:F" & FinalRow).Select
Columns("A:B").EntireColumn.AutoFit
    Rows("1:50").Select
    Selection.RowHeight = 20
Application.ScreenUpdating = True

End Sub
 
Upvote 0
How about
VBA Code: 
Sub Jacko1307()
'
' Jacko1307 Macro

finalrow = Cells(Rows.Count, 1).End(xlUp).Row
 
'
'Application.ScreenUpdating = False
    Columns("F").ClearContents
    Range("D" & finalrow + 1).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
    Range("E" & finalrow + 1).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
    Range("F1:H1").Value = Array("EXPORT", "FARM", "CONTRACT")
    With Range("A1:H" & finalrow)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .RowHeight = 20
        .Borders.Weight = xlThin
    End With
    Columns("F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1").Value = "AVG"
    With Range("F2:F" & finalrow)
      .FormulaR1C1 = "=RC[-1]/RC[-2]"
      .NumberFormat = "0.00"
   End With
Application.ScreenUpdating = True

    Columns("A:B").EntireColumn.AutoFit
End Sub
 
Upvote 0
Fluff thank you that is far simpler than I was doing but where would you insert the code to change columns G H I to column width 25.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
159
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
961
Joe4
Joe4
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
208
Coco1
C
C
  • Solved
How to not set a range in VBA
Replies
7
Views
389
Coco1
C
R
  • Question
Formatting using VBA
Replies
0
Views
416
RandyD123
R

Forum statistics

Threads
1,214,908
Messages
6,122,187
Members
449,071
Latest member
cdnMech

Share this page

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
Back
Top