VBA update cell on worksheet change

[MartinL]

Sorry guys I have to raise this old chestnut

I have a data feed that overwrites my column titles, so my pivot table goes awkward.

All I am trying to do is ensure that Cell "C24" on worksheet "Data"
always has the value "Product" in it. also various other cells that I can populate once ive see what I'm doing wrong.

I have tried

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C24").Select = "" Then _
Range("C24") = "Product"
End Sub

but that don't work.

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C24" And Target.Value = "" Then
    Application.EnableEvents = False
    Target.Value = "Product"
    Application.EnableEvents = True
End If
End Sub

 

[MartinL]

Hi Peter

This only appears to work after I click on C24 and press delete even if the cell is empty.

Then the text "Product" appears.

I am hoping to get the code to run either when I refresh my web selection or even if I add me.calculate to the VBA code

 

[VoG]

That suggests that your data feed doesn't trigger the Worksheet_Change event. Perhaps it will trigger a calculation

Private Sub Worksheet_Calculate()
If Range("D24").Value = "" Then Range("D24").Value = "Product"
End Sub

 

[MartinL]

Eventually I want C24:K24 to contain the values

"Product", "Barcode", "Line", "Sector", "52 Weeks", "26 Weeks", "13 Weeks", "4 Weeks", "1 Week"

I say that because an array may be the way to go, rather than copying the same bit of code 9 times.

 

[VoG]

Then try

Private Sub Worksheet_Calculate()
If Range("C24").Value = "" Then Range("C24:K24").Value = Array("Product", "Barcode", "Line", "Sector", "52 Weeks", "26 Weeks", "13 Weeks", "4 Weeks", "1 Week")
End Sub

 

[MartinL]

Just add me.calculate at the top and wow

VoG you are "King of the Lab"