Variant limit in VBA userforms?

[vhdhfox]

Hi, im currently building a userform spreadsheet to input data as well as query the selected data but currently having an issue with adding more code to the userform.
the code it self is generally identical using match/index worksheet functions,
when I add more lines using Application.WorksheetFunction.Match I get error '1004' unable to get the match property of the worksheetfunction class when I change the code to Application.Match ill get a mismatch match error '13'
it only happens when I add the last bit of code, if I remove it will run fine.

Private Sub ABox1_Change()
    Dim A, B1, C1, D1, E1, F1, G1, H1 As Variant
    Dim B2, C2, D2, E2, F2, G2, H2 As Variant
    Dim B3, C3, D3, E3, F3, G3, H3 As Variant
    Dim B4, C4, D4, E4, F4, G4, H4 As Variant
    Dim B5, C5, D5, E5, F5, G5, H5 As Variant
    Dim B6, C6, D6, E6, F6, G6, H6 As Variant
       
    A = ABox1.Value
    Sheets("Planning").Activate
   
    ABox2 = ABox1.Value
    ABox3 = ABox1.Value
    ABox4 = ABox1.Value
    ABox5 = ABox1.Value
    ABox6 = ABox1.Value

   
    If A = "" Then
        B1 = ""
    Let CtBox1.Text = B1
        C1 = ""
    Let CtrBox1.Text = C1
        D1 = ""
    Let SBox1.Text = D1
        E1 = ""
    Let TBox1.Text = E1
        F1 = ""
    Let DBox1.Text = F1
        G1 = ""
    Let CtnBox1.Text = G1
        H1 = ""
    Let IDB1.Text = H1
 

   
        C2 = ""
    Let CtrBox2.Text = C2
        H2 = ""
    Let IDB2.Text = H2
        B2 = ""
    Let CBox2.Text = B2
        C2 = ""
    Let CtrBox2.Text = C2
        D2 = ""
    Let SBox2.Text = D2
        E2 = ""
    Let TBox2.Text = E2
        F2 = ""
    Let DBox2.Text = F2
        G2 = ""
    Let CtnnBox2.Text = G2
   
        C3 = ""
    Let CtrBox3.Text = C3
        H3 = ""
    Let IDB3.Text = H3
        B3 = ""
    Let CtBox3.Text = B3
        C3 = ""
    Let CtrBox3.Text = C3
        D3 = ""
    Let SBox3.Text = D3
        E3 = ""
    Let TBox3.Text = E3
        F3 = ""
    Let DBox3.Text = F3
        G3 = ""
    Let CtnBox3.Text = G3
        H4 = ""
    Let IDB4.Text = H4
        H5 = ""
    Let IDB5.Text = H5
        H6 = ""
    Let IDB6.Text = H6
       
       
    Else
       
           
        StartRow = 4


        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        H1 = Application.WorksheetFunction.Index(Sheets("Planning").Range("A" & StartRow & ":A100"), SearchRow)
           
    Let IDB1.Text = H1
   
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        C1 = Application.WorksheetFunction.Index(Sheets("Planning").Range("F" & StartRow & ":F100"), SearchRow)
           
    Let CtrBox1.Text = C1
               
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        B1 = Application.WorksheetFunction.Index(Sheets("Planning").Range("H" & StartRow & ":H100"), SearchRow)
           
    Let CBox1.Text = B1
   
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        D1 = Application.WorksheetFunction.Index(Sheets("Planning").Range("C" & StartRow & ":C100"), SearchRow)
           
    Let SBox1.Text = D1
           
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        E1 = Application.WorksheetFunction.Index(Sheets("Planning").Range("G" & StartRow & ":G100"), SearchRow)
           
    Let TBox1.Text = E1
   
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        F1 = Application.WorksheetFunction.Index(Sheets("Planning").Range("b" & StartRow & ":b100"), SearchRow)
           
    Let DBox1.Text = F1
   
 
            StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        G1 = Application.WorksheetFunction.Index(Sheets("Planning").Range("j" & StartRow & ":j100"), SearchRow)
           
    Let CtnBox1.Text = G1
   
   
   
 
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        H2 = Application.WorksheetFunction.Index(Sheets("Planning").Range("A" & StartRow & ":A100"), SearchRow)
           
    Let IDB2.Text = H2
   
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        C2 = Application.WorksheetFunction.Index(Sheets("Planning").Range("F" & StartRow & ":F100"), SearchRow)
           
    Let CtrBox2.Text = C2
   
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        B2 = Application.WorksheetFunction.Index(Sheets("Planning").Range("H" & StartRow & ":H100"), SearchRow)
           
    Let CBox2.Text = B2
   
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        D2 = Application.WorksheetFunction.Index(Sheets("Planning").Range("C" & StartRow & ":C100"), SearchRow)
           
    Let SBox2.Text = D2
           
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        E2 = Application.WorksheetFunction.Index(Sheets("Planning").Range("G" & StartRow & ":G100"), SearchRow)
           
    Let TBox2.Text = E2
   
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        F2 = Application.WorksheetFunction.Index(Sheets("Planning").Range("B" & StartRow & ":B100"), SearchRow)
           
    Let DBox2.Text = F2
   
        StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D30"), 0)
        G2 = Application.WorksheetFunction.Index(Sheets("Planning").Range("J" & StartRow & ":J100"), SearchRow)
           
    Let CtnBox2.Text = G2
   
            StartRow = StartRow + SearchRow
        SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("D" & StartRow & ":D100"), 0)
        H3 = Application.WorksheetFunction.Index(Sheets("Planning").Range("A" & StartRow & ":A100"), SearchRow)
           
    Let IDB3.Text = H3
   
                StartRow = StartRow + SearchRow
        SearchRow = Application.Match(A, Sheets("Planning").Range("D" & StartRow & ":D100"), 0)
        C3 = Application.Index(Sheets("Planning").Range("f" & StartRow & ":f100"), SearchRow)
           
    Let CtrBox3.Text = C3
           
    End If

End Sub

any help with this issue would be greatly appreciated.

 

[RoryA]

That error just means the search value wasn't found. I note that in your first few iterations you're only searching in rows up to row 30, but returning a value from rows up to 100, which doesn't seem wise.

 

[vhdhfox]

That error just means the search value wasn't found. I note that in your first few iterations you're only searching in rows up to row 30, but returning a value from rows up to 100, which doesn't seem wise.

I felt like that was the issue but I found it weird that it works if I change that line to another match/index but when I add another match/index line it will throw the error. So for instance if I remove that last part (CtrBox3) from the code and just change IDB3 to CtrBox3 it will retrieve the data perfectly fine.

Yeah i have been meaning to fix that for awhile but always to forgot haha.

 

[RoryA]

Well you are offsetting the startrow each time, so perhaps you've gone beyond the end of the data?