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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,772
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
38,772
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
38,772
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
38,772
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No problem - glad you got it working! :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,479
Messages
5,831,915
Members
430,091
Latest member
Generally_confused

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
Top