Inserting a value into a cell using VBA

[dasadler]

I am having trouble inserting a zero value into a cell. I am using the code below (from Hans with some tailoring for my needs). The problem is in cell I1... it will not change value. The only thing different about this cell is the custom formatting. The custom formatting is "ANTICIPATED GUESTS: "###

Does this custom format prevent inserting the value of zero?

Code: Select all

Sub Resetvalues()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Dim wsh As Worksheet
Set wsh = Worksheets("EVENT-Work")
Set myRange = wsh.Range("A9:A110, G9:G110,N9:N110,I1,J4,D122,J122,Q122,D118:D119,J118:J119,Q118:Q119,D2")
For Each cl In myRange
If Not IsEmpty(cl.Value) And (cl.Value) <> "QTY" Then cl.Value = "0"
Next cl
wsh.Range("J5").Value = "100"
wsh.Range("I1").Value = "0"
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub

[mbarron]

Your custom formatting is causing the problem. Currently it only allows for positive value. Change it to:
"ANTICIPATED GUESTS: "###;;"ANTICIPATED GUESTS: "0

Custom number formats are in the format of
FormatPositiveValuesLikeThis;FormatNegativeValuesLikeThis;FormatZeroValuesLikeThis

[dasadler]

Thanks makes good logic but it doesn't make a difference for me. It still does not change to zero.

[StuartR]

Thanks makes good logic but it doesn't make a difference for me. It still does not change to zero.

Lets break the problem down into parts.

Can you manually put a zero into the cell and then apply the custom formatting?
If you remove the custom formatting from the cell and run your code what does the value look like?

[dasadler]

Never mind - I think I messed up in the code somewhere... I changed some range information and deleted a column... got to go figure out what I did.

[mbarron]

You are inserting a string 0 into the cell instead of numeric 0

If Not IsEmpty(cl.Value) And (cl.Value) <> "QTY" Then cl.Value = "0"

change to

If Not IsEmpty(cl.Value) And (cl.Value) <> "QTY" Then cl.Value = 0

[dasadler]

Thanks for your thoughts... to be sure, this was my error of deleting columns and not making appropriate range changes in code. Actually, I'm still not sure what I did wrong but I started over and it worked so I am happy.