VBA Range.value = does not populate cell is there is existing data in cell

[DCGNM]

Hi All

I have a simple code blow that i have in my excel. As the Value of F4 changes from time to time, the values to be populated in the Range changes. But i find that if once a value has been populated, the new option cannot overwrite the cell. Ie, if Plan A was chosen, and Cell G5 is already 100000, when plan B is subsequently selected, it would not change to 80000 but stay as 100000.

How can i fix this?

If Sheets("Main").Range("F4").Value = "Plan A" Then
Sheets("GMM").Range("G5").Value = 100000
Sheets("GMM").Range("G8").Value = 10000

ElseIf Sheets("Main").Range("F4").Value = "Plan B" Then
Sheets("GMM").Range("G5").Value = 80000
Sheets("GMM").Range("G8").Value = 8000

End If

 

[RoryA]

That suggests that your code is not running, or F4 is not exactly "Plan B", or there's an error occurring that is being suppressed.

 

[DCGNM]

Hi RoryA

It works when the cells are blank. so if i manually delete the content of the cells and run the code again. it works. I had to add in clear contents, but it works now. but is this the only way? as i have a lot of this code and to apply clear contents above each line would be heavy

[TABLE="width: 133"]
<tbody>[TR]
[TD]If Sheets("Main").Range("F4").Value = "Plan A" Then
Sheets("GMM").Range("G5").ClearContents
Sheets("GMM").Range("G5").Value = 100000
Sheets("GMM").Range("G8").ClearContents
Sheets("GMM").Range("G8").Value = 10000

ElseIf Sheets("Main").Range("F4").Value = "Plan B" Then
Sheets("GMM").Range("G5").ClearContents
Sheets("GMM").Range("G5").Value = 80000
Sheets("GMM").Range("G8").ClearContents
Sheets("GMM").Range("G8").Value = 8000
[/TD]
[/TR]
</tbody>[/TABLE]
End If

 

[RoryA]

That should not be necessary. What's the full code, and how are you calling it?

 

[DCGNM]

Hi RoryA. thanks, i checked the code again and i realise what was wrong. In some of the cells, i was trying to set value as something with symbols in it. It seems tht VBA does not recognise symbols such as $,%,etc. is there a way to fix this?

If Sheets("Main").Range("J4").Value = "$1200, 20% Copay"

 

[RoryA]

That will work as long as that is the actual exact value of the cell (as it appears in the formula bar). Note that it will be case sensitive.

 

[DCGNM]

Hi RoryA

Thank you. Yes the Case Sensitive part was the issue. Managed to find the source an fixed it. sorry for the trouble

 

[RoryA]

No problem - glad you got it working!