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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

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

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

<tbody>
</tbody>
End If
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
That should not be necessary. What's the full code, and how are you calling it?
 

DCGNM

New Member
Joined
Feb 25, 2019
Messages
18

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No problem - glad you got it working! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,054
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top