VBA Excel Change value in the group of cells with Msgbox doesn't work

Question

Good afternoon,

I am trying to change the values in my group of cells (merged cells) with the Msgbox. Unfortunately it doesn't work at all.

My code looks as follows:

Sub Nocivils()
ans = MsgBox("Do you want to remove the civils description?", vbQuestion + vbYesNo)

If ans = Yes Then

   Range("H24:Q32").Select
   ActiveCell.FormulaR1C1 = "N/A"
   Range("H24:Q32").Select
End If

End Sub

I tried previously:

Sub Nocivils()
ans = MsgBox("Do you want to remove the civils description?", vbQuestion + vbYesNo)

If ans = Yes Then

   Activesheet.Range("H24:Q32").Select
   Range("H24:Q32").Value = "N/A
End If

End Sub

And still no result,

Could anyone clarify what am I doing wrong here?

Thanks

`ans = Yes`.... you don't have a variable saying yes. You got a return value of `6` if yes is clicked. I assume thats where you go wrong first (apart from merged cells being evil on it's own btw). — JvdV, Apr 21 '20 at 12:33

JvdV · Accepted Answer · 2020-04-21T12:57:02.657

Apart from merged cells being evil, you first might want to change:

If ans = Yes Then into >> If ans = vbYes Then

Using Option Explicit on the top of your module would have helped catch this un-initialized undeclared variable. Next, have a look at this post on how to avoid the use of Select. You can assign a value directly. Note that the top-left cell of a merged area holds your value which you can change directly using value assignment demonstrated by @BigBen (Range("H24").Value = "N/A"), but don't forget to be explicit about your worksheet at least!

Some documentation on MsgBox function from MS.

score 2 · Answer 2 · answered Apr 21 '20 at 12:42

2

Replace:

If ans = Yes Then

with:

If ans = 6 Then

(Yes is a variable that been neither Dim'ed nor initialized.)

answered Apr 21 '20 at 12:42

Gary's Student

95,722
10
59
99

VBA Excel Change value in the group of cells with Msgbox doesn't work

2 Answers2