CODEWITHSUNDEEP
excelvba
Jimmerding
Jimmerding

Reputation: 5

VBA Code for checking a column has formulas only

I have a template spreadsheet users populate with data and send in to me. One column is a formula, which they keep pasting values over, amongst other input errors. I've handled all the other errors with VBA "before save" code, but cannot figure out the code to have excel check each cell in a column to see if it is a formula and throw a message box up if they have.

I found this article which is quite helpful: https://learn.microsoft.com/en-us/office/vba/api/excel.range.hasformula and this worked to a degree - I dont want a prompt to select range, so I tried with 1 cell or with a set range, EG, L12:L3000 (The users can send in inputs of varying amounts of rows, but 3000 would cover all potentially. If I could incorporate the end(xldown) syntax into it though that would be more ideal!)

I can get it to throw the message box if I select one cell as the range and it is a value but not for multiple cells at once, even if they have a value amongst them. I'm too much of a newbie to figure out Looping despite attempting for a while!

Any help would be greatly appreciated!

Using one cell as a range, using multiple cells in Col L as a range. the code I've got so far works for one cell but not multiple...

If DataInput.[L12].HasFormula = False Then
a = MsgBox("CAUTION: Formula in Column L appears to have been written over. Do you want to Save anyway?", vbYesNo)
If a = vbYes Then Cancel = False
End If

Expected: If there is a value anywhere in Column L, this will loop through and find it. It will then pop up asking for confirmation they want to save, or simply pop up warning formulas have been pasted over and cancel the save.

Upvotes: 0

Views: 834

Answers (1)

SJR
SJR

Reputation: 23081

Does this help? You can use SpecialCells as a shortcut to finding various specific types of cell entry (including formulae).

Sub x()

Dim r As Range

On Error Resume Next
Set r = Range("L1:L10").SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not r Is Nothing Then
    MsgBox "a formula has been overwritten" & vbCr & r.Address
End If

End Sub

enter image description here

Upvotes: 1

Related Questions