Userform: Match imbricked in cells().value

[Sibrulotte]

Hi, I'm doing a tennis reservation file that would enable players to reserve their spot for a specific date.

have a look if you want.
I'm using a userform that lets a player select his name and a date from a calendar.
The OK button in the form would then select the first empty cell of the date's colomne, and put the name found in the combo box.

But I'm not a real VBA user, and I'm always using patchups, so I've only got this so far.

Private Sub OK_Click()
Dim emptyRow As Long


Sheets("Insc").Activate
emptyRow = WorksheetFunction.CountA(Range("D:D")) + 2

Date = Calendrier.Value
range_lookup = Sheets("Insc").Range("D3:AZ3")
Colonne_index = Application.Match(Date, range_lookup)
Cells(emptyRow, Colonne_index).Value = Nom.Value

Nom.Value = ""

End Sub

So the cells (empty row, part works, but I'm trying to add the colomne number to be a Match of the date from the calender value. Not realy successful.
Can anyone give a pointer?

[Sibrulotte]

no replies then

[Sibrulotte]

I guess noone likes tennis...

[cytop]

Hit the first problem here...

Date = Calendrier.Value

Date is a reserved word in VBA and you cannot assign a value to it, otherwise you're changing the system date.

After changing the variable name, and picking Thurs 9th May, it inserted the player name in Col AQ - I assume this should have been Col F or G...?

[Sibrulotte]

It should have been F in deed.
I had tried that , using DatePartie for the calendar date. But I still get blocked at RangeLookup.

Private Sub OK_Click()
Dim emptyRow As Long
Dim DatePartie As String
Dim RangeLookup As String
Dim Colonne_index As String


Sheets("Insc").Activate
emptyRow = WorksheetFunction.CountA(Range("D:D")) + 2

DatePartie = Calendrier.Value
RangeLookup = Sheets("Insc").Range("D3:AZ3")Colonne_index = Application.Match(DatePartie, RangeLookup, 0)
Cells(emptyRow, Colonne_index).Value = Nom.Value

Nom.Value = ""

End Sub

How did you get it to put a value in AQ? And why would that return AQ for Thuursday may 9th? Weird, AQ is called "Statut"...

[cytop]

using DatePartie for the calendar date

Posting code you know is wrong is not helpful - it just means someone else has to debug other issues just to get to the issue you post...

[Sibrulotte]

I tried changing the Date to DatePartie after posting here, sorry if it bothered you.
Sooooo, even if it is wrong, would you mind posting the code that returned AQ ? I'd work from there

[cytop]

Strangely enough, it now errors with Error 2042. Will have to come back to this, I'm out of time today. Will check back later if you have no other takers.

[Sibrulotte]

Thank you for the time taken.

[Norie]

Try this for finding the column for the selected date.

Colonne_index = Application.Match(CLng(Calendrier.Value), range_lookup, 0)+3

After you've found the column use this to find the next empty row.

empty_row = Sheets("Insc").Cells(Rows.Count, Colonne_Index).End(xlUp).Row+1

Now you can put the name in.

Sheets("Insc").Cells(empty_row, Colonne_index).Value = Nom.Value

You'll also need to add checks for the date being found and that there is a space for the player if the date is found.

So the code would look like this.

Private Sub OK_Click()
Dim range_lookup As Range
Dim emptyRow As Long
Dim Colonne_Index As Long

    Set range_lookup = Sheets("Insc").Range("D3:AZ3")

    Colonne_Index = Application.Match(CLng(Calendrier.Value), range_lookup, 0) + 3
    If Not IsError(Colonne_Index) Then
        empty_row = Sheets("Insc").Cells(Rows.Count, Colonne_Index).End(xlUp).Row + 1

        If empty_row < 10 Then
            Sheets("Insc").Cells(empty_row, Colonne_Index).Value = Nom.Value
        End If
    End If
    
End Sub

[Sibrulotte]

Ah that works prime!
I'm wondering though, the If not Iserror doesn't have an else in the case it is an error. Right now, this just pops up a debugging window. I tried putting an else after the first end if with a msgbox, but it still debugs at Colonne_index = ...

I'm not restraining the number of players, we have unlimited court space. I have a formula next to the player name (Only in Colomn E right now) that determines wether the player is offsetting pairs, and is waiting for another player to register. I'll add a msgbox at the end with that cell to indicate wether his reservation was taken or if he is "en attente"--> pending.

[Norie]

You can get rid of the error by declaring Colonne_index as Variant instead of Long, then it can handle the error value.

If you don't want to restrict the number of players remove this and it's corresponding End If

If empty_row < 10 Then

I'm not sure what your formula is for but you shouldn't have formulas copied down to rows without data.

If you need to add 'en attente' or whatever next to the player you can use code.