sharky12345
Well-known Member
- Joined
- Aug 5, 2010
- Messages
- 3,431
- Office Version
- 2016
- Platform
- Windows
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;
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;
and then;
Please someone tell me that it is easy to resolve because I am pulling my hair out!
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;
Code:
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;
Code:
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;
Code:
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!