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

DCGNM

New Member
Joined
Feb 25, 2019
Messages
18
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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
 
Upvote 0
That should not be necessary. What's the full code, and how are you calling it?
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
Hi RoryA

Thank you. Yes the Case Sensitive part was the issue. Managed to find the source an fixed it. sorry for the trouble
 
Upvote 0
No problem - glad you got it working! :)
 
Upvote 0

Forum statistics

Threads
1,222,118
Messages
6,164,072
Members
451,870
Latest member
Nikhil excel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top