Compile Error - variable not defined

[sharky12345]

The following code has been used previously to enter data from a userform to a worksheet without a problem. However, since I added some new bits of code I am getting a compile error with the message variable not defined.

Here is part of what I have so far and the bit that is highlighted after the error comes up is the 'Set ws' line;

Private Sub CommandButton1_Click()
Worksheets("Duties").Range("C5") = txtdate
Worksheets("Duties").Range("H5") = txtarea
Worksheets("Duties").Range("N5") = txttea
Set ws = Worksheets("Duties")
'find  first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

The code I have recently added with the kind help of Norie is as follows but I'm not convinced that is causing the problem;

Private Sub UserForm_Initialize()
Set wsRes = Worksheets("Resources")
    With wsRes
        .Range("B1", .Range("B" & Rows.Count).End(xlUp)).AdvancedFilter xlFilterCopy, , .Range("L1"), True
        With .Range("L2", .Range("L" & Rows.Count).End(xlUp))
            cboTeam.List = .Value
            .EntireColumn.Clear
        End With
    End With
End Sub

and then;

Option Explicit
Dim wsRes As Worksheet
Private Sub cboNumber_Change()
Dim rngFnd As Range
Dim strMember As String
    If cboNumber.ListIndex <> -1 Then
        strMember = cboNumber.Column(1)
    Else
        With wsRes
            Set rngFnd = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Find(cboNumber.Value)
        End With
        If Not rngFnd Is Nothing Then
            strMember = rngFnd.Offset(, 1)
        End If
 
    End If
    TextBox1.Value = strMember
End Sub
Private Sub cboNumber2_Change()
Dim rngFnd As Range
Dim strMember As String
    If cboNumber2.ListIndex <> -1 Then
        strMember = cboNumber2.Column(1)
    Else
        With wsRes
            Set rngFnd = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Find(cboNumber2.Value)
        End With
        If Not rngFnd Is Nothing Then
            strMember = rngFnd.Offset(, 1)
        End If
 
    End If
    TextBox2.Value = strMember
End Sub
Private Sub cboTeam_Change()
Dim rng As Range
Dim cl As Range
    If cboTeam.ListIndex <> -1 Then
 
    cboNumber.Clear
    With wsRes
 
    Set rng = .Range("B2", .Range("D" & Rows.Count).End(xlUp))
 
    End With
    For Each cl In rng.Cells
 
        If cl.Value = cboTeam.Value Then
            cboNumber.AddItem cl.Offset(, 1)
            cboNumber.List(cboNumber.ListCount - 1, 1) = cl.Offset(, 2)
            cboNumber2.AddItem cl.Offset(, 1)
            cboNumber2.List(cboNumber.ListCount - 1, 1) = cl.Offset(, 2)
        End If
    Next cl
    End If
End Sub

Please someone tell me that it is easy to resolve because I am pulling my hair out!

 

[mikerickson]

The ws variable wasnt declared. Try

Private Sub CommandButton1_Click()
Dim ws As Worksheet

Worksheets("Duties").Range("C5") = txtdate
Worksheets("Duties").Range("H5") = txtarea
Worksheets("Duties").Range("N5") = txttea
Set ws = Worksheets("Duties")
'find  first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

 

[sharky12345]

Mike,

Many thanks - but now the following is highlighted after getting the same error message;

iRow = ws.Cells(Rows.Count, 1) _

 

[mikerickson]

You have to declare all your variables including iRow.

 

[sharky12345]

OK, didn't know that - can you help me with the line of code and where it would go please? I'm quite a novice with variables!

 

[mikerickson]

The VBEditor Help (keyword Dim) gives instruction on declaring variables.

 

[sharky12345]

Mike,

I am still struggling with this - I have searched as you suggested but I still can't figure it out, is there any other resource that I can check out?

 

[Simon Lloyd]

Further discussion & solutions to this thread are here http://www.thecodecage.com/forumz/m...amming/211964-variable-not-defined-error.html

 

[sharky12345]

Guys,

This is now resolved - many thanks for all your help, your suggestions have worked a treat!