Excel Help

mad_allan

mad_allan

mad_allan

Soldato
Joined
17 Dec 2003
Posts
3,250
Location
Peterborough
Hi all,

I am in need of some help :D

Basically, I have a spreadsheet with numerous formulas, etc.. which I am fine with.

However, I am trying to figure out a way to overwrite a formula with plain text BUT the formula remains intact. So, if the plain text is deleted, the formula will then populate the cell.

I have had a quick play around in VB but struggling.

Does anyone have a VB script that could help?

Cheers
 
This should hopefully get you started

Right click the worksheet and select 'view code' then paste this code in there. It runs the code every time a cell changes.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range) 'Target is a range of all detected changes, it could be 1 or multiple cells
    
    Dim DETECT As Range
    Dim RNG As Range
    
    'Insert below the range you want to detect for changes, I've used A1:J10 as an example
    Set DETECT = Application.Intersect(Range("A1:J10"), Range(Target.Address))
    
    'If no changes in your detection range then exit sub
    If DETECT Is Nothing Then Exit Sub
        
    For Each RNG In DETECT 'loop through all the changed cells in your detection range
        If RNG.Formula = "" Then 'Check if formula is blank (i.e. has been deleted)
            RNG.Formula = "=RAND()" 'Your formula here, probably best to use the R1C1 format for relative cell references
        End If
    Next

End Sub
 
Last edited:
I think I am having one of those days :(

Tried but no luck. I changed the range to my cell range.

My formula is =IF(BD7:BD23="W","READY FOR CALCULATION",IF(BD7:BD23="Y","COMPLETE",IF(BD7:BD23="N","")))

If I type Yes, for example, then delete, yes, the cell is then blank
 
You must log in or register to reply here.
Back
Top Bottom