Error with VBA code

[hsolanki]

Hi can anybody fix me this formula which is for the User form

Sub Addme()

'declare the variables
Dim Addme As Range
Dim x As Integer
'error handler
On Error GoTo errHandler:
'find the next black row in the database
Sheet1.Unprotect Password:="Bhaji2019"
With Sheet1.Range("c710000") ' Simply Change the range to suit
.Locked = False
.FormulaHidden = False
If Application.WorksheetFunction.CountIf(Sheet1.Range("G:G").Me.cboBooked) > 0 Then
MsgBox "on a Job"
Me.cboBooked.Value = ""
Exit Sub
Else: Addme

End If
End With
Set Addme = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
'loop through multi selected items and add them to the database
For x = 0 To Me.lstSelector.ListCount - 1
If Me.lstSelector.Selected(x) Then

Addme = Me.cbodatetime
Addme.Offset(0, 1) = Me.lstSelector.List(x)
Addme.Offset(0, 2) = Me.cboBooked.Value
Addme.Offset(0, 3) = Me.txtJob.Value
Addme.Offset(0, 4) = Me.lstSelector.List(x, 3)
Addme.Offset(0, 5) = Me.lstSelector.List(x, 4)
Addme.Offset(0, 6) = Me.lstSelector.List(x, 5)
Addme.Offset(0, 7) = Me.lstSelector.List(x, 6)
Addme.Offset(0, 8) = Me.lstSelector.List(x, 7)
Addme.Offset(0, 9) = Me.lstSelector.List(x, 8)
Set Addme = Addme.Offset(1, 0)
End If
Next x
'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please Contact Harin Solanki"

End Sub

 

[hsolanki]

i am getting an error
on below line

If Application.WorksheetFunction.CountIf(Sheet1.Range("G:G").Me.cboBooked) > 0 Then

 

[FormR]

Hi, the full stop (period) after the Range("G:G") part should probably be a comma.

If Application.WorksheetFunction.CountIf(Sheet1.Range("G:G"),Me.cboBooked) > 0 Then

 

[hsolanki]

Hi Thanks for prompt response

however it is not recognizing duplicates when i am entering data from a user form.

 

[FormR]

however it is not recognizing duplicates when i am entering data from a user form.

Hi, I'm sorry but you have not provided any context that would allow me to comment on the functionality of your code.

 

[hsolanki]

Copy of Staff Allocation-Database Final Version V6.xlsm

Open

 

[hsolanki]

i have uploaded an complete file

bascially i want give an option if the individual is Booked on a Job then if you try to Book him on another Job then msgbox should says already on a Job and give the user to be able to choose Yes / No and if user says Yes then add the data on the sheet 1 and if No then nothing

 

[FormR]

i have uploaded an complete file

Hi, it's not much help I'm afraid. I don't have the time to try to learn how to use your application.

If your application.countif() function doesn't return you desired results then try to debug why that might be, if you try for example, to enter the equivalent countif() function directly on the worksheet does it return the expected result?

 

[hsolanki]

I really appropriate your help but i am not an expert on VBA

i have managed to get the message comes up however it is sill adding the data on to the sheet now

 

[FormR]

Hi, try adding a breakpoint to your sub and stepping through the code to better understand what is happening.

i am not an expert on VBA

The link below might help.

https://www.myonlinetraininghub.com/debugging-vba-code