Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change case...help please | Excel Worksheet Functions | |||
Using other workbooks.. | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) |