Allround_it_er
New Member
- Joined
- Aug 5, 2022
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Below some code to search if the entered number through a combobox is higher than a number in a column.
The code works fine for everything I want to do, except for the last condition:
CboZoekProduct = 1012 and LastCell = 5045
although 1012 is not > then 5045
the msgbox appears.
How is that possible?
Below complete event:
Help is very much appreciated. Thx
The code works fine for everything I want to do, except for the last condition:
VBA Code:
ElseIf cboZoekProduct > LastCell Then
MsgBox "In de afdeling 'Traiteur' is het laatste artikelnummer " & LastCell & "." & vbNewLine & _
"Het getal mag dus niet groter zijn dan " & LastCell & "!!", vbCritical, "Getal groter dan " & LastCell & "!"
End If
CboZoekProduct = 1012 and LastCell = 5045
although 1012 is not > then 5045
the msgbox appears.
How is that possible?
Below complete event:
VBA Code:
'*****************************************************************************
' Is the number higher than the highest number starting with 1, 2, 3, 4 or 5 *
'*****************************************************************************
Dim rij1 As Long
Dim rij2 As Long
Dim rij3 As Long
Dim rij4 As Long
Dim found1 As Range
Dim found2 As Range
Dim found3 As Range
Dim found4 As Range
Dim CellValue1 As String
Dim CellValue2 As String
Dim CellValue3 As String
Dim CellValue4 As String
Dim LastCellA As Range
Dim LastCell As Range
Set found1 = Sheets("Producten_nieuw").Columns("A").Find(What:="2***", LookIn:=xlValues, lookat:=xlWhole)
rij1 = found1.Row - 1
CellValue1 = Range("A" & rij1).Value
Set found2 = Sheets("Producten_nieuw").Columns("A").Find(What:="3***", LookIn:=xlValues, lookat:=xlWhole)
rij2 = found2.Row - 1
CellValue2 = Range("A" & rij2).Value
Set found3 = Sheets("Producten_nieuw").Columns("A").Find(What:="4***", LookIn:=xlValues, lookat:=xlWhole)
rij3 = found3.Row - 1
CellValue3 = Range("A" & rij3).Value
Set found4 = Sheets("Producten_nieuw").Columns("A").Find(What:="5***", LookIn:=xlValues, lookat:=xlWhole)
rij4 = found4.Row - 1
CellValue4 = Range("A" & rij4).Value
Set LastCell = Sheets("Producten_nieuw").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
LastCell.Select
Set LastCell = LastCell.Offset(0, -2)
'Set LastCellA = Range("A" & Rows.Count).End(xlUp) 'second way to determine value of last cell in column "A"
If cboZoekProduct > CellValue1 And cboZoekProduct < 2000 Then
MsgBox "In de afdeling 'Brood' is het laatste artikelnummer " & CellValue1 & "." & vbNewLine & _
"Het getal mag dus niet groter zijn dan " & CellValue1 & "!!", vbCritical, "Getal is groter dan " & CellValue1 & "!"
ElseIf cboZoekProduct > CellValue2 And cboZoekProduct < 3000 Then
MsgBox "In de afdeling 'Diepvries' is het laatste artikelnummer " & CellValue2 & "." & vbNewLine & _
"Het getal mag dus niet groter zijn dan " & CellValue2 & "!!", vbCritical, "Getal is groter dan " & CellValue2 & "!"
ElseIf cboZoekProduct > CellValue3 And cboZoekProduct < 4000 Then
MsgBox "In de afdeling 'Boterkoeken' is het laatste artikelnummer " & CellValue3 & "." & vbNewLine & _
"Het getal mag dus niet groter zijn dan " & CellValue3 & "!!", vbCritical, "Getal is groter dan " & CellValue3 & "!"
ElseIf cboZoekProduct > CellValue4 And cboZoekProduct < 5000 Then
MsgBox "In de afdeling 'Patisserie' is het laatste artikelnummer " & CellValue4 & "." & vbNewLine & _
"Het getal mag dus niet groter zijn dan " & CellValue4 & "!!", vbCritical, "Getal is groter dan " & CellValue4 & "!"
'ElseIf cboZoekProduct > LastCell And cboZoekProduct < 5999 Then
ElseIf cboZoekProduct > LastCell Then
MsgBox "In de afdeling 'Traiteur' is het laatste artikelnummer " & LastCell & "." & vbNewLine & _
"Het getal mag dus niet groter zijn dan " & LastCell & "!!", vbCritical, "Getal groter dan " & LastCell & "!"
End If
Help is very much appreciated. Thx