Unable to resolve type mismatch with userform entry

[DeanP]

I cannot resolve a Type mismatch error that I have with a userform.


I have 2 textboxes in my userform. One for the year to be entered and the second for the month in mmm format. The 1st
textbox stores year value which will be copied to a cell in the worksheet when the form is closed. The value entered in
the 2nd box, is the lookup for a 3rd textbox. The lookup event happens when an OK button is clicked.


In my worksheet in Z1:Y6 I have a list X: Jan Y: 01 Z: 12/01/19. I get they type mismatch error when, after the month is
entered, the OK button is clicked.

Debug highlights this line of code:


For x = 1 To wsLR


Here is the rest of the code



Private Sub CommandButton1_Click()
Dim wsJournal As Worksheet
Set wsJournal = Worksheets("Journal")
With wsJournal
.Cells(4, 5).Value = Me.TextBox6.Value
.Cells(4, 6).Value = Me.TextBox8.Value
.Cells(4, 3).Value = Me.TextBox9.Value
.Cells(4, 8).Value = Me.TextBox10.Value
End With
Call FuncAllocJnl
Unload (UserForm1)
End Sub


Private Sub CommandButton2_Click()
Unload (UserForm1)
Set UserForm1 = Nothing
End Sub


Private Sub CommandButton3_Click()
' This sub routine autofills the boxes in the userform frame based on what was entered
Call TextBox8_Change
End Sub


Private Sub TextBox8_Change()
'Lookups from the worksheet to fill the boxes: month name into period number
Dim wsJournal As Worksheet
Dim wsLR As Range

Set wsJournal = Worksheets("Journal")
Set wsLR = wsJournal.Range("X1:Z6")
x = 1

For x = 1 To wsLR
If wsJournal.Cells(x, 1) = Me.TextBox7 Then
Me.TextBox8 = wsLR.Cells(x, 2)
End If
Next x
End Sub


Private Sub TextBox9_Change()
'Period number to date range
Dim wsJournal As Worksheet
Dim wsLR As Range
Set wsJournal = Worksheets("Journal")
Set wsLR = wsJournal.Range("X1:Z6")
x = 1

For x = 1 To wsLR
If wsJournal.Cells(x, 2) = Me.TextBox8 Then
Me.TextBox9 = wsLR.Cells(x, 3)
End If
Next x
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Prevent users from closing form with red cancel
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You can't close the form like this!"
End If
End Sub



I don't know what I'm doing wrong here. Any advice would be appreciated.

[Jan Karel Pieterse]

Your variable wsLR contains a reference to the range X1:Z6. the For loop in which you try to use wsLR expectes a numerical value for wsLR, which is NOT the same as a pointer to a range of cells. Hence the type mismatch. How should the end-point of this for-next loop be determined?