userform question

[plost33]

I have a userform which allows user to selected a date formt he calendar. In my code I name the value selected myDate. I want to have a line of code at the begginning of my code that checks if myDate = a date already listed in column H on a sheet named "all dor's". if it does match then a msgbox appear saying "date already entered" and the code return to the opened userform.

 

[plost33]

it goes for about 3000 rows. and screen updating is off, but i was hoping to fix with a one line formula like vog was suggesting.

 

[plost33]

if it make a big diffrence i an in 07 excel.

 

[plost33]

any ideas why the part in red is not working to find a match in column H? if a match is found then it should show userform4, or basically return to the user form for my users to select another date:

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim Mylrow As Long, PLLrow As Long
Dim MyDate As Date, MyVsle As String
Dim R
Mylrow = Sheets("All DOR'S").Range("B18").End(xlDown).Row + 1
With UserForm4
MyDate = Calendar1.Value
End With
Sheets("all dor's").Activate
With ActiveSheet
If IsNumeric(Application.Match(MyDate, Range("H18:H" & Mylrow), 0)) Then
MsgBox "DOR of the date DATE has already been entered! Please select another date."
UserForm4.Show
else
End If
End With
Sheets("All DOR'S").Cells(Mylrow, 2).PasteSpecial xlValues
With UserForm4
MyDate = Calendar1.Value

For R = 0 To .lstbVessel.ListCount - 1
If .lstbVessel.Selected(R) = True Then ''get the vessle name
MyVsle = lstbVessel.List(R)
End If
Next
'''' now det the last row form the pasted values '''
PLLrow = Sheets("All DOR'S").Range("B65536").End(xlUp).Row
'''' fill column H for Date and Column I for Vessle '''
Sheets("All DOR'S").Range("H" & Mylrow & ":H" & PLLrow).Value = MyDate '' this is for the Date
Sheets("All DOR'S").Range("I" & Mylrow & ":I" & PLLrow).Value = MyVsle
End With
Unload UserForm4
MsgBox "DOR data added successfully!"
End Sub

 

[plost33]

after numerous step thrus, this line is not appearently matching the values in my cells:

If IsNumeric(Application.Match(MyDate, ("H18:H" & Mylrow), 0)) = 1 Then


i have a matching date and everything but it is not finding it. ideas??

 

[VoG]

You need to remove the =1 for one thing.

 

[VoG]

Try this. Declare MyDate as a Date. Then

If IsNumeric(Application.Match(CDbl(MyDate), Range("H18:H30"), 0)) Then

 

[plost33]

vog, that fixed it. thanks alot .

 

[plost33]

vog: how can i add in to also check for the variable named "MyVsle". it is not defined until later in the code? can you show me where I would need to move it up in the code?

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim Mylrow As Long, PLLrow As Long
Dim MyDate As Date, MyVsle As String
Dim R
Mylrow = Sheets("All DOR'S").Range("B18").End(xlDown).Row + 1
With UserForm4
    MyDate = Calendar1.Value
    End With
        
    Sheets("all dor's").Activate
    With ActiveSheet
    If IsNumeric(Application.Match(CDbl(MyDate), Range("H18:H" & Mylrow), 0)) Then
    MsgBox "A DOR of the same date has already been entered! Please select another date."
    Exit Sub
    Else
    End If
    
    End With
Sheets("All DOR'S").Cells(Mylrow, 2).PasteSpecial xlValues
With UserForm4
    MyDate = Calendar1.Value
   
    For R = 0 To .lstbVessel.ListCount - 1
        If .lstbVessel.Selected(R) = True Then ''get the vessle name
            MyVsle = lstbVessel.List(R)
        End If
    Next
    '''' now det the last row form the pasted values '''
    PLLrow = Sheets("All DOR'S").Range("B65536").End(xlUp).Row
    '''' fill column H for Date and Column I for Vessle '''
    Sheets("All DOR'S").Range("H" & Mylrow & ":H" & PLLrow).Value = MyDate   '' this is for the Date
    Sheets("All DOR'S").Range("I" & Mylrow & ":I" & PLLrow).Value = MyVsle
End With
Unload UserForm4
MsgBox "DOR data added successfully!"
End Sub

 

[VoG]

Try

    For R = 0 To .lstbVessel.ListCount - 1
        If .lstbVessel.Selected(R) = True Then ''get the vessle name
            MyVsle = lstbVessel.List(R)
        End If
    Next
    If IsNumeric(Application.Match(MyVsle, Range("I18:I" & Mylrow), 0)) Then
        MsgBox "Error message goes here."
        Exit Sub
    End If

 

[plost33]

is that going to mean the same thing as:

If IsNumeric(Application.Match(CDbl(MyDate) & CDbl(MyVsle), Range("H18:H" & Mylrow), 0)) Then


i am wanting the code to check for both, and both must be true in order to show the msgbox, if both are not true then the code can paste as usual.