Desperate to Find Error

R

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
701
Office Version
  1. 365
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
To find those errors Debug one line at a time by pressing F8. Start at the Userform1.Show...
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Find XLookup #N/A Errors and Copy Row and PasteValues into a new list on new worksheet
Replies
2
Views
286
kyddrivers
K
R
  • Solved
VBA User selecting file now the code errors Type Mismatch
2 3 4
Replies
30
Views
2K
Alex Blakenburg
A
M
  • Question
can't execute code in break mode!!
Replies
2
Views
2K
Mayozero
M
R
  • Question
Updating Current Row from ComboBox
Replies
2
Views
542
reberryjr
R
R
  • Question
Sorting a Dynamic Range with Multiple Variables
Replies
7
Views
729
MARK858
MARK858

Forum statistics

Threads
1,214,962
Messages
6,122,482
Members
449,088
Latest member
Melvetica

Share this page

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
Back
Top