Const mRange as String error...

Noob101

New Member
Joined
Jan 10, 2014
Messages
18
Hello,

I'm still at the very basics of VBA and I would really love it if someone could help me out because this stuff is so tricky!

Basically, I am learning how to add more rules using VBA (and I have a few more to go) but I'm kind of stuck because I keep getting the "Const mRange as String error" appearing in my Excel 2003 macro. Below is the code - any help would be fantastic!!

Private Sub Worksheet_Change(ByVal Target As Range)

Const mRange As String = "O21:BH450"
Dim myPlage As Range, c As Range
Set myPlage = Me.Range(mRange)
For Each c In myPlage
With c
Select Case UCase(.Value)
Case "N"
.Interior.ColorIndex = 38
Case "N/A"
.Interior.ColorIndex = 48
Case "EXEMPT"
.Interior.ColorIndex = 48
Case "Y"
.Interior.ColorIndex = 4
Case "NOMINATED"
.Interior.ColorIndex = 41
Case "ENROLLED"
.Interior.ColorIndex = 6
Case ""
.Interior.ColorIndex = 38
Case Else
.Interior.ColorIndex = xlNone
End Select
End With
Next c
Const mRange As String = "BJ21:BJ450"
Dim myPlage As Range, c As Range

Set myPlage = Me.Range(mRange)

For Each c In myPlage
With c
Select Case UCase(.Value)
Case "N/A"
.Interior.ColorIndex = 48
Case ""
.Interior.ColorIndex = 38
Case Else
.Interior.ColorIndex = xlNone
End Select
End With
Next c
End Sub

Any thoughts??
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
Hello,

I'm still at the very basics of VBA and I would really love it if someone could help me out because this stuff is so tricky!

Basically, I am learning how to add more rules using VBA (and I have a few more to go) but I'm kind of stuck because I keep getting the "Const mRange as String error" appearing in my Excel 2003 macro. Below is the code - any help would be fantastic!!

Private Sub Worksheet_Change(ByVal Target As Range)

Const mRange As String = "O21:BH450"
Dim myPlage As Range, c As Range
Set myPlage = Me.Range(mRange)
For Each c In myPlage
With c
Select Case UCase(.Value)
Case "N"
.Interior.ColorIndex = 38
Case "N/A"
.Interior.ColorIndex = 48
Case "EXEMPT"
.Interior.ColorIndex = 48
Case "Y"
.Interior.ColorIndex = 4
Case "NOMINATED"
.Interior.ColorIndex = 41
Case "ENROLLED"
.Interior.ColorIndex = 6
Case ""
.Interior.ColorIndex = 38
Case Else
.Interior.ColorIndex = xlNone
End Select
End With
Next c
Const mRange As String = "BJ21:BJ450"
Dim myPlage As Range, c As Range

Set myPlage = Me.Range(mRange)

For Each c In myPlage
With c
Select Case UCase(.Value)
Case "N/A"
.Interior.ColorIndex = 48
Case ""
.Interior.ColorIndex = 38
Case Else
.Interior.ColorIndex = xlNone
End Select
End With
Next c
End Sub

Any thoughts??
The keyword Const is short for "constant"... and a constant is just that... constant. You are not allowed to change a constant once you have defined it. The line the error is occurring on is your attempt to assign a new value to the constant mRange that you defined at the top of your code. If you need to change the value assigned to it during your code, then do not make it a constant... just Dim it a normal variable of type String.
 

Noob101

New Member
Joined
Jan 10, 2014
Messages
18
Hi Rick!

Sorry for the hassle and thankyou for helping me out. I'm still new at this - could you provide an example code answer (because my boss wants this done by next week).

You guys/gals are the best!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
Hi Rick!

Sorry for the hassle and thankyou for helping me out. I'm still new at this - could you provide an example code answer (because my boss wants this done by next week).
It sounds like you might need to step back and learn how to program VBA before you go on, or you will end up in trouble with your boss as he is going to assume you know how program at a level you are not ready for. The change I mentioned you should do is quite basic (no pun intended) and should not have been difficult for you to implement. I'll change your code, but you really need to understand what it is that I did in case you are ever asked to modify this code in the future. Verbally, what I have done is remove both of your Const statements and replaced them with simple assignment statements (shown in red) and I also declared mRange as a variable of type String in your existing Dim statement (shown in blue). I also remove this second Dim statement...

Dim myPlage As Range, c As Range

because you already had this same line at the top of your code (you can only Dim a variable once within any one code procedure). I have also indented your code because that makes it much more readable (you may have actually done that with your own code, but you did not use code tags around your code, so this forum's comment processor left justified all of the text in your code).

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)

  Dim myPlage As Range, c As Range, mRange As String

  mRange = "O21:BH450"
  Set myPlage = Me.Range(mRange)
  For Each c In myPlage
    With c
      Select Case UCase(.Value)
        Case "N"
          .Interior.ColorIndex = 38
        Case "N/A"
          .Interior.ColorIndex = 48
        Case "EXEMPT"
          .Interior.ColorIndex = 48
        Case "Y"
          .Interior.ColorIndex = 4
        Case "NOMINATED"
          .Interior.ColorIndex = 41
        Case "ENROLLED"
          .Interior.ColorIndex = 6
        Case ""
          .Interior.ColorIndex = 38
        Case Else
          .Interior.ColorIndex = xlNone
        End Select
    End With
  Next c
  
  mRange = "BJ21:BJ450"
  Set myPlage = Me.Range(mRange)
  For Each c In myPlage
    With c
    Select Case UCase(.Value)
      Case "N/A"
        .Interior.ColorIndex = 48
      Case ""
        .Interior.ColorIndex = 38
      Case Else
        .Interior.ColorIndex = xlNone
    End Select
    End With
  Next c
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,515
Members
414,074
Latest member
Matthew Kakde

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