VBA Excel applying a simple division across tabs

Question

the following code runs the invalid use of property error. I am just trying to apply a simple division across 50 tabs. its basically dividing each cell value with one cell value at the top.

Sub Macro2()
'
' Macro2 Macro
  Dim ws As Worksheet`
   For Each ws In ThisWorkbook.Worksheets
         ws.Range ("AD1")
         ActiveCell.FormulaR1C1 = "In %"
         ws.Range ("AD1")
         Selection.Font.Bold = True
         ws.Range ("AD2")
         Application.CutCopyMode = False
         Application.CutCopyMode = False
         ActiveCell.FormulaR1C1 = "=RC[-2]/R2C28"
         ws.Range ("AD2")
         Selection.Copy
         ws.Range ("AD2:AD91")
         Application.CutCopyMode = False
         Selection.FillDown
         Selection.Style = "Percent"
         Selection.NumberFormat = "0.0%"
         Selection.Font.Bold = True
         ws.Range ("AD2")
    Next ws
    End Sub

Which line errors? What is the intention behind a line such as `ws.Range ("AD2")`? — SJR, Jul 18 '18 at 13:57
Ws. Range ("AD1") is where the error pops up. That is basically the empty cells where the calculation should start. — DK7, Jul 18 '18 at 14:02
well, because `ws.Range ("AD1")` does nothing at all. It is no valid syntax. Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). This will fix your issues. — Pᴇʜ, Jul 18 '18 at 14:06

score 4 · Answer 1 · answered Jul 18 '18 at 14:06

Lose the Selects which are inefficient and unnecessary and your code can be shortened to

Sub Macro2()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
     ws.Range("AD1").Value = "In %"
     ws.Range("AD1").Font.Bold = True
     With ws.Range("AD2:AD91")
        .FormulaR1C1 = "=RC[-2]/R2C28"
        .Style = "Percent"
        .NumberFormat = "0.0%"
        .Font.Bold = True
     End With
Next ws

End Sub

VBA Excel applying a simple division across tabs

1 Answers1