Inserting a value into a cell using VBA

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Inserting a value into a cell using VBA

Post by 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
Don

User avatar
mbarron
2StarLounger
Posts: 112
Joined: 25 Jan 2010, 20:19

Re: Inserting a value into a cell using VBA

Post by 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
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Inserting a value into a cell using VBA

Post by dasadler »

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

User avatar
StuartR
Administrator
Posts: 12697
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Inserting a value into a cell using VBA

Post by StuartR »

dasadler wrote: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?
StuartR


dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Inserting a value into a cell using VBA

Post by 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.
Don

User avatar
mbarron
2StarLounger
Posts: 112
Joined: 25 Jan 2010, 20:19

Re: Inserting a value into a cell using VBA

Post by 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
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Inserting a value into a cell using VBA

Post by 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.
Don