What's wrong with my code ?
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 |
Perhaps you should clearly explain what you are trying to do.
-- Don Guillett SalesAid Software "christophe meresse" wrote in message ... 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 |
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" wrote: 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 |
Oki doki, I see my problem now, Thank you for your help "Rowan" a écrit dans le message de ... 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" wrote: 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 |
All times are GMT +1. The time now is 05:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com