What's wrong with my code ?

[christophe meresse]

Hi !

I'm trying this code but I have a code error 9
Can you help me ?


Private Sub Cmd_Valid_Click()
Dim c As Integer
c = 1
For c = 1 To 12
If Range("R28").Value = c Then
Zone = "Feuil" & c + 4
End If
Next c
MsgBox "Selected sheet is" & Zone
LaPremiereDispo = Sheets(Zone).Range("E65536").End(xlUp).Offset(1, 0).Row
MsgBox "the first cell for this month is" & LaPremiereDispo
End Sub

 

[Don Guillett]

Perhaps you should clearly explain what you are trying to do.

 

[Guest]

You will get this error if you do not have a sheet with a name that matches
the variable zone. Also you do not need the loop. Try it like this:

Private Sub Cmd_Valid_Click()
Dim sht As Worksheet
Dim LaPremiereDispo As Long
Dim zone As String
If Range("R28").Value >= 1 And Range("R28").Value <= 12 Then
zone = "Feuil" & Range("R28").Value + 4
MsgBox "Selected sheet is " & zone
On Error Resume Next
Set sht = Sheets(zone)
On Error GoTo 0
If sht Is Nothing Then
MsgBox "Sheet does not exist"
Else
LaPremiereDispo = sht.Range("E65536").End(xlUp).Offset(1, 0).Row
MsgBox "the first cell for this month is " & LaPremiereDispo
End If
End If
End Sub

Hope this helps
Rowan

 

[christophe meresse]

Oki doki, I see my problem now,

Thank you for your help