Forum Discussion
Rodrigo_
Sep 17, 2024Steel Contributor
Formula (VBA generated) fills the entire column of selected cell
Hello everyone
I'm currently experiencing an issue with my Excel (365). The code is supposed to insert a formula into a cell when I double-click it, but it fills the entire column of the selected cell with the formula.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r As Integer
If Not Intersect(Target, Me.Range("F3:R65")) Is Nothing And IsEmpty(Target.Value) Then
r = Target.Row
Target.Formula = "=IF($C" & r & ">$D" & r & ",$D" & r & "+1-$C" & r & ",$D" & r & "-$C" & r & ")"
Cancel = True
End If
End Sub
Before:
After (Double-clicked cell I15):
3 Replies
Sort By
- JKPieterseSilver Contributor
Rodrigo_ Your data is in a table. In tables it is good practice (as in many other areas of a spreadsheet) to have the same formula in all rows (one that can be copied across), rather than ad-hoc formulas all over the place. That being said, your problem is being caused by a setting that enforces this: If you type a formula in an empty column of a table, Excel automatically copies that formula to all cells of that column. That can be turned off (though I do not recommend it!):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim r As Integer If Not Intersect(Target, Me.Range("F3:R65")) Is Nothing And IsEmpty(Target.Value) Then r = Target.Row Application.AutoCorrect.AutoFillFormulasInLists = False Target.Formula = "=IF($C" & r & ">$D" & r & ",$D" & r & "+1-$C" & r & ",$D" & r & "-$C" & r & ")" Application.AutoCorrect.AutoFillFormulasInLists = True Cancel = True End If End Sub
- Rodrigo_Steel ContributorThe thing is, I've been using this data/table/format for a couple of years (using excel 2016-19) without this kind of problem, but when I switched M365 recently, it suddenly occurs. Don't know why..
- JKPieterseSilver ContributorWell, some behavioral changes are to be expected when switching to a new version.