I'm thoroughly confused as to why this code works sometimes, but not others.
What I'm trying to do:
The first code has the User select a value from cobo_Nickname (this is working). The code then searches a particular worksheet for that value, and populates the txt_ClientID value, that is in that row. Sometimes this works and sometimes it doesn't and I'm not sure why. The second code reads the value of the txt_ClientID and accesses the worksheet with the same name as that value. It then finds the first instance of "Late" in column CE, and returns various values from that row. This part isn't working at all.
This is particularly concerning because the vast majority of the application I'm building, uses this type of practice. What I don't understand is why the first code works some of the time and not others.
What I'm trying to do:
The first code has the User select a value from cobo_Nickname (this is working). The code then searches a particular worksheet for that value, and populates the txt_ClientID value, that is in that row. Sometimes this works and sometimes it doesn't and I'm not sure why. The second code reads the value of the txt_ClientID and accesses the worksheet with the same name as that value. It then finds the first instance of "Late" in column CE, and returns various values from that row. This part isn't working at all.
This is particularly concerning because the vast majority of the application I'm building, uses this type of practice. What I don't understand is why the first code works some of the time and not others.
Code:
Private Sub cobo_Nickname_Change()
Dim ws3 As Worksheet
Dim FindRow As Range
Dim Nick As Long
Set ws3 = ThisWorkbook.Sheets("Bios")
With ws3
'Finds the row where the value of the cobo_RefNickame exists, and populates the RefID combobox with the value of the Client ID, from the same row.
Set FindRow = Range("J:J").Find(What:=Me.cobo_Nickname, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If Not FindRow Is Nothing Then
Nick = FindRow.Row
Else
Exit Sub
End If
End With
Me.txt_ClientID = ws3.Range("E" & Nick).Value
End Sub
Private Sub txt_ClientID_Change()
Dim Sht As String
Dim LastRow As Long
Dim UpdateRow As Long
Dim FindRow As Range
Sht = Me.txt_ClientID
With ActiveSheet
LastRow = .Cells(.Rows.Count, "CE").End(xlUp).Row
Set FindRow = Range("CE:CE").Find(What:="Late", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If Not FindRow Is Nothing Then
UpdateRow = FindRow.Row
End If
End With
On Error Resume Next
Me.txt_Name = Sheets(Sht).Range("E" & UpdateRow).Value
'Me.cobo_Nickname = Sheets(Sht).Range("F" & UpdateRow).Value
Me.txt_DPStatus = Sheets(Sht).Range("G" & UpdateRow).Value
Me.txt_DPDelq = Sheets(Sht).Range("L" & UpdateRow).Value
Me.txt_TPStatus = Sheets(Sht).Range("M" & UpdateRow).Value
Me.txt_TPDelq = Sheets(Sht).Range("R" & UpdateRow).Value
Me.txt_TRStatus = Sheets(Sht).Range("S" & UpdateRow).Value
Me.txt_TRNextDue = Sheets(Sht).Range("Y" & UpdateRow).Value
Me.txt_TRDelq = Sheets(Sht).Range("AB" & UpdateRow).Value
Me.txt_DCNextDue = Sheets(Sht).Range("AJ" & UpdateRow).Value
Me.txt_DCPymtStatus = Sheets(Sht).Range("AK" & UpdateRow).Value
Me.txt_DCDelq = Sheets(Sht).Range("AM" & UpdateRow).Value
Me.txt_OCNextDue = Sheets(Sht).Range("AU" & UpdateRow).Value
Me.txt_OCPymtStatus = Sheets(Sht).Range("AV" & UpdateRow).Value
Me.txt_OCDelq = Sheets(Sht).Range("AX" & UpdateRow).Value
Me.txt_CTINextDue = Sheets(Sht).Range("BF" & UpdateRow).Value
Me.txt_CTIPymtStatus = Sheets(Sht).Range("BG" & UpdateRow).Value
Me.txt_CTIDelq = Sheets(Sht).Range("BI" & UpdateRow).Value
Me.txt_CTONextDue = Sheets(Sht).Range("BQ" & UpdateRow).Value
Me.txt_CTOPymtStatus = Sheets(Sht).Range("BR" & UpdateRow).Value
Me.txt_CTODelq = Sheets(Sht).Range("BT" & UpdateRow).Value
Me.txt_TotalDue = Format(Sheets(Sht).Range("BV" & UpdateRow).Value, "#0.00")
Me.txt_Wk1 = Format(Sheets(Sht).Range("BW" & UpdateRow).Value, "#0.00")
Me.txt_Wk2 = Format(Sheets(Sht).Range("BX" & UpdateRow).Value, "#0.00")
Me.txt_Wk3 = Format(Sheets(Sht).Range("BY" & UpdateRow).Value, "#0.00")
Me.txt_Wk4 = Format(Sheets(Sht).Range("BZ" & UpdateRow).Value, "#0.00")
Me.txt_Wk5 = Format(Sheets(Sht).Range("CA" & UpdateRow).Value, "#0.00")
Me.txt_FundsRcvdToDate = Format(Sheets(Sht).Range("CB" & UpdateRow).Value, "#0.00")
Me.txt_InReserve = Format(Sheets(Sht).Range("CC" & UpdateRow).Value, "#0.00")
Me.txt_NetDue = Format(Sheets(Sht).Range("CD" & UpdateRow).Value, "#0.00")
End Sub