vba help! Compile error: " Constant expression required"

[Xceller]

Thank you in advance!

I am trying make row(Lrow) and column(Lcol) as variables to be used in Lbound and Ubound that require constant expression.

Sub dynArr()
Dim WSD As Worksheet
Dim Lrow As Long
Dim Lcol As Long
Dim nProd(4 To Lrow) As Long
Dim nMth(5 To Lcol) As Long

ReDim nProd(4 To Lrow)
ReDim nMth(5 To Lcol)

Set WSD = ThisWorkbook.Worksheets("Data")

Lrow = WSD.Cells(Rows.Count, "D").End(xlUp).Row
Lcol = WSD.Cells("E", Columns.Count).End(xlToLeft).Column


For r = LBound(nProd) To UBound(nProd)
For c = LBound(nMth) To UBound(nMth)

If Cells(r, c).Value > 500 Then
Cells(r, c).Interior.Color = RGB(0, 255, 0)
End If

Next c
Next r


End Sub

 

[AlphaFrog]

ReDim nProd and nMth after you set the values for Lrow and Lcol.

 

[AlphaFrog]

Maybe try something like this...

[color=darkblue]Sub[/color] dynArr()
    
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range, Lrow [color=darkblue]As[/color] [color=darkblue]Long[/color], Lcol [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]With[/color] ThisWorkbook.Worksheets("Data")
    
        Lrow = .Cells(Rows.Count, "D").End(xlUp).Row
        Lcol = .Cells(4, Columns.Count).End(xlToLeft).Column
    
        [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] .Range("D4", Cells(Lrow, Lcol))
    
            [color=darkblue]If[/color] cell.Value > 500 [color=darkblue]Then[/color]
                cell.Interior.Color = RGB(0, 255, 0)
            [color=darkblue]Else[/color]
                cell.Interior.ColorIndex = xlNone
            [color=darkblue]End[/color] [color=darkblue]If[/color]
    
        [color=darkblue]Next[/color] cell
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Xceller]

Thanks AlphaFrog for your quick response. I moved the Redim statement, but it's still giving me the same error msg:
Sub dynArr()
Dim WSD As Worksheet
Dim Lrow As Long
Dim Lcol As Long
Dim nProd(4 To Lrow) As Long
Dim nMth(5 To Lcol) As Long

Set WSD = ThisWorkbook.Worksheets("Data")

Lrow = WSD.Cells(Rows.Count, "D").End(xlUp).Row
Lcol = WSD.Cells(5, Columns.Count).End(xlToLeft).Column

ReDim nProd(4 To Lrow)
ReDim nMth(5 To Lcol)

For r = LBound(nProd) To UBound(nProd)
For c = LBound(nMth) To UBound(nMth)

If Cells(r, c).Value > 500 Then
Cells(r, c).Interior.Color = RGB(0, 255, 0)
End If

Next c
Next r

End Sub

 

[AlphaFrog]

Thanks AlphaFrog for your quick response. I moved the Redim statement, but it's still giving me the same error msg:

Dim nProd() As Long
Dim nMth() As Long

Also remove Lrow and Lcol from the Dim statements.

 

[Xceller]

Thank AlphaFrog. Your code works perfectly. I am trying to understand the Lbound(n+1) to Ubound(n+1) construct in other use of arrays. Can n be dimensioned as a variable? In my case, Lrow and Lcol. I want them to be dynamic.

 

[Xceller]

It works perfectly. Thanks again AlphaFrog.

 

[AlphaFrog]

Thank AlphaFrog. Your code works perfectly. I am trying to understand the Lbound(n+1) to Ubound(n+1) construct in other use of arrays. Can n be dimensioned as a variable? In my case, Lrow and Lcol. I want them to be dynamic.

Yes n can be a variable, but the order you do things is important.

First define an array with no dimensions.
Dim MyArray() as Long

Then set the value for n
n = 10

Then ReDim the array using n
ReDim MyArray(0 to n)

Does that help?