Looking for an error in VBA-Excel Data validation code

Question

I am currently working on a entry data checking vba-code and this is what i got so far. The code is supposed to mark values that are out of range and throw an error when the wrong datatype is entered. Right now the code throws an error for every entry in the table even if it is in range. I do not find the problem though. Does anyone now where I made a mistake?

Sub CheckColumns()
Dim rng As Range
Dim lCol As Long, lRow As Long
Dim DblWidthMax As Double
Dim DblHeightMax As Double
Dim DblLengthMin As Double
Dim DblWidthMin As Double
Dim DblHeightMin As Double
Dim DblLengthMax As Double


DblLenghtMax = 20000
DblWidthMax = 20000
DblHeightMax = 20000
DblLengthMin = 5
DblWidthMin = 5
DblHeightMin = 5

    lCol = Range("C2").End(xlToRight).Column
    lRow = Range("C2").End(xlDown).Row

    For Each rng In Range("C2", Cells(lRow, lCol))
        If IsNumeric(rng) = False Then
          MsgBox ("A number has to be entered " & "Row " & rng.Row 
          & " Column " & rng.Column)
          rng.Font.ColorIndex = 3
    End If

       If rng.Value > DblLengthMax Or rng.Value < DblLengthMin Then
         MsgBox ("Value in " & "Row " & rng.Row & " Column " & 
         rng.Column & " is out of range. Check for unit (mm)")
         rng.Font.ColorIndex = 46
        
      End If

    Next rng

End Sub

So for example I enter the values: 3000, 50, 3. for length width and height. Since my range is 5 to 20000 the first two values should be in Range and not be colored. Right now every value is colored orange. I would only expect the 3 to be coloured

Because DblLenghtMin is spelt differently in your declarations and initial value setting to the version in your if statement (DblLengthMin). — Jos Woolley
– Jos Woolley, Commented Jul 22, 2022 at 9:13
@JosWoolley thank you! I fixed it, unfortunately it doesn't fix the problem — FMT
– FMT, Commented Jul 22, 2022 at 9:22
It did for me when I tested. I trust you made sure that all three occurrences of that name are now the same? — Jos Woolley
– Jos Woolley, Commented Jul 22, 2022 at 9:24
I checked and it doesn't work still. I posted an example of enry data and the output in the text. @JosWoolley — FMT
– FMT, Commented Jul 22, 2022 at 9:34

Foxfire And Burns And Burns · Accepted Answer · 2022-07-22 09:47:19Z

Your variables have not been properly declared. As example, you have

Dim DblLengthMax As Double

But then you do:

DblLenghtMax = 20000

Notice is not the same variable!!!! So DblLengthMax is initialized as 0. Then your code does:

If rng.Value > DblLengthMax Or rng.Value < DblLengthMin Then but remember because of your typo DblLengthMax=0 so any value greater than 0 will be colored...

ALWAYS use Option Explicit command to avoid these errors and typos:

Option Explicit statement

Try like this:

Option Explicit

Sub CheckColumns()
Dim rng As Range
Dim lCol As Long, lRow As Long
Dim DblWidthMax As Double
Dim DblHeightMax As Double
Dim DblLengthMin As Double
Dim DblWidthMin As Double
Dim DblHeightMin As Double
Dim DblLengthMax As Double


DblLengthMax = 20000
DblWidthMax = 20000
DblHeightMax = 20000
DblLengthMin = 5
DblWidthMin = 5
DblHeightMin = 5

    lCol = Range("C2").End(xlToRight).Column
    lRow = Range("C2").End(xlDown).Row

    For Each rng In Range("C2", Cells(lRow, lCol))
        If IsNumeric(rng) = False Then
          MsgBox ("A number has to be entered " & "Row " & rng.Row & " Column " & rng.Column)
          rng.Font.ColorIndex = 3
    End If

       If rng.Value > DblLengthMax Or rng.Value < DblLengthMin Then
         MsgBox ("Value in " & "Row " & rng.Row & " Column " & rng.Column & " is out of range. Check for unit (mm)")
         rng.Font.ColorIndex = 46
        
      End If

    Next rng
End Sub

Collectives™ on Stack Overflow

Looking for an error in VBA-Excel Data validation code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related