1

I am able to find the cells which contain the formulas, and even can select them. Now i want to insert apostrophe in front of the formulas of the selected cells.

This is my code :

Sub FindFormulaCells()
Dim inputRange As Range
Set inputRange = Application.InputBox("Select a Range:", "Insert Apostrophe in front of formula", , , , , , 8)

If inputRange Is Nothing Then Exit Sub
Set inputRange = inputRange.SpecialCells(xlCellTypeFormulas, 23)
inputRange.Select
End Sub

Any help.

Aman Devrath
  • 398
  • 1
  • 3
  • 21

1 Answers1

5

Never use Select in code

Sub FindFormulaCells()
Dim inputRange As Range
Set inputRange = Application.InputBox("Select a Range:", "Insert Apostrophe in front of formula", , , , , , 8)

If inputRange Is Nothing Then Exit Sub
Set inputRange = inputRange.SpecialCells(xlCellTypeFormulas, 23)
dim c as range
for each c in inputRange
 c.formula = "'" & c.formula
next c
End Sub
Harassed Dad
  • 4,669
  • 1
  • 10
  • 12
  • Thankyou so much it worked. Can you explain me why "never use select in code"? – Aman Devrath Jun 12 '18 at 12:22
  • It's never needed, it is incredibly slow to execute and it makes the code very difficult to read. Explicitly referring to cells, or using a variable to point to ranges is both faster and easier to follow – Harassed Dad Jun 12 '18 at 12:24
  • Oh. Learned something new. Thanks for the answer :) – Aman Devrath Jun 12 '18 at 12:26
  • @Aman Devrath In this particular case it makes good sense to use `Select`, since it highlights the cells containing formulas that have been updated. It's actually a case of "you should never say never". – Miqi180 Jun 12 '18 at 12:36
  • 1
    @AmanDevrath there's a nice little article about how to avoid using select (and also other so-called "`Active`" Elements) [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) – Samuel Hulla Jun 12 '18 at 13:38
  • Thank you @Rawrplus – Aman Devrath Jun 13 '18 at 07:47