how can use array with this long lines code?

[leap out]

hello
I have this code but it's very slow if anybody has make shorter by using array I truly appreciate

Private Sub CommandButton1_Click()
 Dim ws As Worksheet, matchFormula1, matchFormula2, matchFormula13 As String, cb1, cb2, cb3, cb4, cb5, cb6, cb7, cb8, cb9 As String
    Set ws = Sheets("Sheet1")
    cb1 = ComboBox1.Text
    cb2 = ComboBox2.Text
    cb3 = ComboBox3.Text
    cb4 = ComboBox4.Text
    cb5 = ComboBox5.Text
    cb6 = ComboBox6.Text
    cb7 = ComboBox7.Text
    cb8 = ComboBox8.Text
    cb9 = ComboBox9.Text
    
    matchFormula1 = "match(" & Chr(34) & cb1 & cb2 & cb3 & Chr(34) & ",A:A&B:B&C:C,0)"
    matchFormula2 = "match(" & Chr(34) & cb4 & cb5 & cb6 & Chr(34) & ",A:A&B:B&C:C,0)"
   matchFormula3 = "match(" & Chr(34) & cb7 & cb8 & cb9 & Chr(34) & ",A:A&B:B&C:C,0)"
    On Error Resume Next
    ws.Cells(Evaluate(matchFormula1), ws.Columns.Count).End(xlToLeft).Offset(, 1) = TextBox1.Value
    ws.Cells(Evaluate(matchFormula2), ws.Columns.Count).End(xlToLeft).Offset(, 1) = TextBox2.Value
    ws.Cells(Evaluate(matchFormula3), ws.Columns.Count).End(xlToLeft).Offset(, 1) = TextBox3.Value
    On Error GoTo 0
End Sub

 

[mart37]

You use complete columns to match. It is better to search for the last row and match till this last row.

 

[leap out]

thanks but honestly it shows slowly when copy data from user form to sheet

 

[mart37]

Because the matchformula.
The range A:A&B:B&C:C is the same as A1:A1048576&B1:B1048576&C1:C1048576
When this is more compact like A1:A1000&B1:B1000&C1:C1000 then your code is faster.

 

[leap out]

you're right it's fast ,but is there any way the code just search for the data which is filling rows without search for empty rows to doesn't make slow the code ?

 

[mart37]

I used as example row 1000.
In practice you search the last row of your data like:

lastrow=ws.Range("A" & Rows.Count).End(xlUp).Row

 

[leap out]

this part of amending code but it doesn't copy anything , can you guide me please?

lastrow = ws.Range("C" & Rows.Count).End(xlUp).Row

    matchFormula1 = "match(" & Chr(34) & cb1 & cb2 & cb3 & Chr(34) & "(,A1:A & LR) & (B1:B & LR) & (C1:C & LR ),0)"
    matchFormula2 = "match(" & Chr(34) & cb4 & cb5 & cb6 & Chr(34) & "(,A1:A & LR) & (B1:B & LR) & (C1:C & LR ),0)"
   matchFormula3 = "match(" & Chr(34) & cb7 & cb8 & cb9 & Chr(34) & "(,A1:A & LR) & (B1:B & LR) & (C1:C & LR ),0)"

 

[mart37]

matchFormula1 = "match(" & Chr(34) & cb1 & cb2 & cb3 & Chr(34) & ",A1:A" & lastrow &" & B1:B" & lastrow & " & C1:C" & lastrow & ",0)"

 

[leap out]

unfortunately nothing changes

 

[mart37]

I have no problems.
Can you switch off the ON ERROR statement.
Do you get an error?