Desperate to Find Error

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
When I try to test this code, I'm getting an Error Type 13, but I haven't been able to find where there's a data mismatch. Thoughts?
Code:
Option Explicit

Dim coboDict As Object
Private Sub cmd_Close_Click()


Unload Me


End Sub


Private Sub cmd_Submit_Click()


Dim ws1 As Worksheet
Dim LastRow As Long


Set ws1 = ThisWorkbook.Sheets("Info")


LastRow = ws1.Range("G" & Rows.Count).End(xlUp).Row + 1


ws1.Range("A" & LastRow).Value = "=Today()"
ws1.Range("B" & LastRow).Value = CDate(Me.txt_Updated)
ws1.Range("C" & LastRow).Value = (Me.cobo_Status)
ws1.Range("D" & LastRow).Value = (Me.txt_First)
ws1.Range("E" & LastRow).Value = (Me.txt_Last)
ws1.Range("F" & LastRow).Value = (Me.txt_Suff)
ws1.Range("G" & LastRow).Value = (Me.cobo_Name)
ws1.Range("H" & LastRow).Value = CDate(Me.txt_DoB)
ws1.Range("I" & LastRow).Value = (Me.cobo_Gender)
ws1.Range("J" & LastRow).Value = (Me.txt_SignupAge)
ws1.Range("L" & LastRow).Value = (Me.txt_Phone)
ws1.Range("M" & LastRow).Value = (Me.txt_Email)
If Not Len(Me.txt_DPStart) = 0 Then ws1.Range("O" & LastRow).Value = CDate(Me.txt_DPStart)
If Not Len(Me.txt_DPEnd) = 0 Then ws1.Range("P" & LastRow).Value = CDate(Me.txt_DPEnd)
If Not Len(Me.txt_DPAmt) = 0 Then ws1.Range("Q" & LastRow).Value = CCur(Me.txt_DPAmt)
If Not Len(Me.cobo_DPFreq) = 0 Then ws1.Range("R" & LastRow).Value = (Me.cobo_DPFreq)
If Not Len(Me.txt_DCStart) = 0 Then ws1.Range("T" & LastRow).Value = CDate(Me.txt_DCStart)
If Not Len(Me.txt_DCEnd) = 0 Then ws1.Range("U" & LastRow).Value = CDate(Me.txt_DCEnd)
If Not Len(Me.txt_DCAmt) = 0 Then ws1.Range("V" & LastRow).Value = CCur(Me.txt_DCAmt)
If Not Len(Me.cobo_DCFreq) = 0 Then ws1.Range("W" & LastRow).Value = (Me.cobo_DCFreq)
If Not Len(Me.txt_OCStart) = 0 Then ws1.Range("Y" & LastRow).Value = CDate(Me.txt_OCStart)
If Not Len(Me.txt_OCEnd) = 0 Then ws1.Range("Z" & LastRow).Value = CDate(Me.txt_OCEnd)
If Not Len(Me.txt_OCAmt) = 0 Then ws1.Range("AA" & LastRow).Value = CCur(Me.txt_OCAmt)
If Not Len(Me.cobo_OCFreq) = 0 Then ws1.Range("AB" & LastRow).Value = (Me.cobo_OCFreq)
If Not Len(Me.txt_CTIStart) = 0 Then ws1.Range("AD" & LastRow).Value = CDate(Me.txt_CTIStart)
If Not Len(Me.txt_CTIEnd) = 0 Then ws1.Range("AE" & LastRow).Value = CDate(Me.txt_CTIEnd)
If Not Len(Me.txt_CTIAmt) = 0 Then ws1.Range("AF" & LastRow).Value = CCur(Me.txt_CTIAmt)
If Not Len(Me.cobo_CTIFreq) = 0 Then ws1.Range("AG" & LastRow).Value = (Me.cobo_CTIFreq)
If Not Len(Me.txt_CTOStart) = 0 Then ws1.Range("AI" & LastRow).Value = CDate(Me.txt_CTOStart)
If Not Len(Me.txt_CTOEnd) = 0 Then ws1.Range("AJ" & LastRow).Value = CDate(Me.txt_CTOEnd)
If Not Len(Me.txt_CTOAmt) = 0 Then ws1.Range("AK" & LastRow).Value = CCur(Me.txt_CTOAmt)
If Not Len(Me.cobo_CTOFreq) = 0 Then ws1.Range("AL" & LastRow).Value = (Me.cobo_CTOFreq)
ws1.Range("N" & LastRow).Value = "=IF(RC[1]="""",""Inactive"",IF(RC[2]<>"""",""Inactive"",""Active""))"
ws1.Range("S" & LastRow).Value = "=IF(RC[1]="""",""Inactive"",IF(RC[2]<>"""",""Inactive"",""Active""))"
ws1.Range("X" & LastRow).Value = "=IF(RC[1]="""",""Inactive"",IF(RC[2]<>"""",""Inactive"",""Active""))"
ws1.Range("AC" & LastRow).Value = "=IF(RC[1]="""",""Inactive"",IF(RC[2]<>"""",""Inactive"",""Active""))"
ws1.Range("AH" & LastRow).Value = "=IF(RC[1]="""",""Inactive"",IF(RC[2]<>"""",""Inactive"",""Active""))"


End Sub


Private Sub cobo_Name_Change()


With Sheets("Info")
    Me.txt_Updated = .Cells(coboDict.Item(Me.cobo_Name.Value), "B").Value
    Me.cobo_Status = .Cells(coboDict.Item(Me.cobo_Name.Value), "C").Value
    Me.txt_First = .Cells(coboDict.Item(Me.cobo_Name.Value), "D").Value
    Me.txt_Last = .Cells(coboDict.Item(Me.cobo_Name.Value), "E").Value
    Me.txt_Suff = .Cells(coboDict.Item(Me.cobo_Name.Value), "F").Value
    Me.txt_DoB = .Cells(coboDict.Item(Me.cobo_Name.Value), "H").Value
    Me.cobo_Gender = .Cells(coboDict.Item(Me.cobo_Name.Value), "I").Value
    Me.txt_SignupAge = .Cells(coboDict.Item(Me.cobo_Name.Value), "J").Value
    Me.txt_Phone = .Cells(coboDict.Item(Me.cobo_Name.Value), "L").Value
    Me.txt_Email = .Cells(coboDict.Item(Me.cobo_Name.Value), "M").Value
    Me.cobo_DPStatus = .Cells(coboDict.Item(Me.cobo_Name.Value), "N").Value
    Me.txt_DPStart = .Cells(coboDict.Item(Me.cobo_Name.Value), "O").Value
    Me.txt_DPEnd = .Cells(coboDict.Item(Me.cobo_Name.Value), "P").Value
    Me.txt_DPAmt = .Cells(coboDict.Item(Me.cobo_Name.Value), "Q").Value
    Me.cobo_DPFreq = .Cells(coboDict.Item(Me.cobo_Name.Value), "R").Value
    Me.cobo_DCStatus = .Cells(coboDict.Item(Me.cobo_Name.Value), "S").Value
    Me.txt_DCStart = .Cells(coboDict.Item(Me.cobo_Name.Value), "T").Value
    Me.txt_DCEnd = .Cells(coboDict.Item(Me.cobo_Name.Value), "U").Value
    Me.txt_DCAmt = .Cells(coboDict.Item(Me.cobo_Name.Value), "V").Value
    Me.cobo_DCFreq = .Cells(coboDict.Item(Me.cobo_Name.Value), "W").Value
    Me.cobo_OCStatus = .Cells(coboDict.Item(Me.cobo_Name.Value), "X").Value
    Me.txt_OCStart = .Cells(coboDict.Item(Me.cobo_Name.Value), "Y").Value
    Me.txt_OCEnd = .Cells(coboDict.Item(Me.cobo_Name.Value), "Z").Value
    Me.txt_OCAmt = .Cells(coboDict.Item(Me.cobo_Name.Value), "AA").Value
    Me.cobo_OCFreq = .Cells(coboDict.Item(Me.cobo_Name.Value), "AB").Value
    Me.cobo_CTIStatus = .Cells(coboDict.Item(Me.cobo_Name.Value), "AC").Value
    Me.txt_CTIStart = .Cells(coboDict.Item(Me.cobo_Name.Value), "AD").Value
    Me.txt_CTIEnd = .Cells(coboDict.Item(Me.cobo_Name.Value), "AE").Value
    Me.txt_CTIAmt = .Cells(coboDict.Item(Me.cobo_Name.Value), "AF").Value
    Me.cobo_CTIFreq = .Cells(coboDict.Item(Me.cobo_Name.Value), "AG").Value
    Me.cobo_CTOStatus = .Cells(coboDict.Item(Me.cobo_Name.Value), "AH").Value
    Me.txt_CTOStart = .Cells(coboDict.Item(Me.cobo_Name.Value), "AI").Value
    Me.txt_CTOEnd = .Cells(coboDict.Item(Me.cobo_Name.Value), "AJ").Value
    Me.txt_CTOAmt = .Cells(coboDict.Item(Me.cobo_Name.Value), "AK").Value
    Me.cobo_CTOFreq = .Cells(coboDict.Item(Me.cobo_Name.Value), "AL").Value


End With
End Sub


Private Sub UserForm_Initialize()


Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws4 As Worksheet
Dim cGender As Range
Dim cPymtFreq As Range
Dim cCIName As Range
Dim cStatus As Range


Set ws1 = ThisWorkbook.Sheets("Info")
Set ws2 = ThisWorkbook.Sheets("Measurements")
Set ws4 = ThisWorkbook.Sheets("Variables")


For Each cGender In ws4.Range("Gender")
    With Me.cobo_Gender
        .AddItem cGender.Value
    End With
Next cGender


For Each cStatus In ws4.Range("Status")
    With Me.cobo_Status
        .AddItem cStatus.Value
    End With
Next cStatus


For Each cPymtFreq In ws4.Range("PymtFreq")
    With Me.cobo_DPFreq
        .AddItem cPymtFreq.Value
    End With
Next cPymtFreq


For Each cPymtFreq In ws4.Range("PymtFreq")
    With Me.cobo_DCFreq
        .AddItem cPymtFreq.Value
    End With
Next cPymtFreq


For Each cPymtFreq In ws4.Range("PymtFreq")
    With Me.cobo_OCFreq
        .AddItem cPymtFreq.Value
    End With
Next cPymtFreq


For Each cPymtFreq In ws4.Range("PymtFreq")
    With Me.cobo_CTIFreq
        .AddItem cPymtFreq.Value
    End With
Next cPymtFreq


For Each cPymtFreq In ws4.Range("PymtFreq")
    With Me.cobo_CTOFreq
        .AddItem cPymtFreq.Value
    End With
Next cPymtFreq


    Range("F1").Select
    ActiveWorkbook.Worksheets("Info").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Info").Sort.SortFields.Add Key:= _
        Range("F2:F8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Info").Sort.SortFields.Add Key:= _
        Range("B2:B8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Info").Sort
        .SetRange Range("A1:R8")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
Set coboDict = CreateObject("Scripting.Dictionary")
With coboDict
    For Each cCIName In ws1.Range("CIName")
        If Not .exists(cCIName.Value) Then
            .Add cCIName.Value, cCIName.Row
        Else
            If CLng(cCIName.Offset(, -4).Value) > CLng(ws1.Range("B" & .Item(cCIName.Value))) Then
            .Item(cCIName.Value) = cCIName.Row
            End If
        End If
    Next cCIName
    Me.cobo_Name.List = Application.Transpose(.keys)
End With
    
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,177
Office Version
  1. 365
Platform
  1. Windows
To find those errors Debug one line at a time by pressing F8. Start at the Userform1.Show...
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
Thanks. I stepped through the code multiple times last night, prior to posting the question, but couldn't identify the error. I decided to leave it and come back to it this morning, with fresh eye. I found the error. I inserted a new column that I accounted for in all of the other code, but forgot to change a column reference from -4 to -5. Thank you for the reply!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,168
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top