Variant limit in VBA userforms?

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Aug 6, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Well you are offsetting the startrow each time, so perhaps you've gone beyond the end of the data?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,614
Messages
5,659,846
Members
418,532
Latest member
roynaz11

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