Updating code running slow!

[asyamonique]

Hello,
My worksheet has datas from the columns A:AI all the way down..

Userform with a listbox populates those datas with filtering by combobox.

When I clıck any data from listbox it populates the other comboboxes with given codes below.

Private Sub ListBox1_Click()
Me.TextBox1.Value = Me.ListBox1.Column(0)
Me.TextBox3.Value = Me.ListBox1.Column(1)
Me.TextBox4.Value = Me.ListBox1.Column(2)
Me.ComboBox3.Value = Me.ListBox1.Column(3)
Me.ComboBox4.Value = Me.ListBox1.Column(4)
Me.ComboBox5.Value = Me.ListBox1.Column(5)
Me.ComboBox6.Value = Me.ListBox1.Column(6)
Me.ComboBox7.Value = Me.ListBox1.Column(7)
Me.ComboBox8.Value = Me.ListBox1.Column(8)
Me.ComboBox9.Value = Me.ListBox1.Column(9)
Me.ComboBox10.Value = Me.ListBox1.Column(10)
Me.ComboBox11.Value = Me.ListBox1.Column(11)
Me.ComboBox12.Value = Me.ListBox1.Column(12)
Me.ComboBox13.Value = Me.ListBox1.Column(13)
Me.ComboBox14.Value = Me.ListBox1.Column(14)
Me.ComboBox15.Value = Me.ListBox1.Column(15)
Me.ComboBox16.Value = Me.ListBox1.Column(16)
Me.ComboBox17.Value = Me.ListBox1.Column(17)
Me.ComboBox18.Value = Me.ListBox1.Column(18)
Me.ComboBox19.Value = Me.ListBox1.Column(19)
Me.ComboBox20.Value = Me.ListBox1.Column(20)
Me.ComboBox21.Value = Me.ListBox1.Column(21)
Me.ComboBox22.Value = Me.ListBox1.Column(22)
Me.ComboBox23.Value = Me.ListBox1.Column(23)
Me.ComboBox24.Value = Me.ListBox1.Column(24)
Me.ComboBox25.Value = Me.ListBox1.Column(25)
Me.ComboBox26.Value = Me.ListBox1.Column(26)
Me.ComboBox27.Value = Me.ListBox1.Column(27)
Me.ComboBox28.Value = Me.ListBox1.Column(28)
Me.ComboBox29.Value = Me.ListBox1.Column(29)
Me.ComboBox30.Value = Me.ListBox1.Column(30)
Me.ComboBox31.Value = Me.ListBox1.Column(31)
Me.ComboBox32.Value = Me.ListBox1.Column(32)
Me.ComboBox33.Value = Me.ListBox1.Column(33)


Me.TextBox5.Value = Me.ListBox1.Column(34)


End Sub

The purpose of doing that I do update if there are any changes from any column entry with the given code below.

Dim erowa As Integer
Dim x As Integer
erowa = Application.WorksheetFunction.CountA(Sheet1.Range("AI:AI"))
For x = 1 To erowa


If Sheet1.Cells(x, "AI").Value = Me.TextBox5.Text Then


Sheet1.Cells(x, "A").Value = Me.TextBox1.Text
Sheet1.Cells(x, "B").Value = Me.TextBox3.Text
Sheet1.Cells(x, "C").Value = Me.TextBox4.Text
Sheet1.Cells(x, "D").Value = Me.ComboBox3.Text
Sheet1.Cells(x, "E").Value = Me.ComboBox4.Text
Sheet1.Cells(x, "F").Value = Me.ComboBox5.Text
Sheet1.Cells(x, "G").Value = Me.ComboBox6.Text
Sheet1.Cells(x, "H").Value = Me.ComboBox7.Text
Sheet1.Cells(x, "I").Value = Me.ComboBox8.Text
Sheet1.Cells(x, "J").Value = Me.ComboBox9.Text
Sheet1.Cells(x, "K").Value = Me.ComboBox10.Text
Sheet1.Cells(x, "L").Value = Me.ComboBox11.Text
Sheet1.Cells(x, "M").Value = Me.ComboBox12.Text
Sheet1.Cells(x, "N").Value = Me.ComboBox13.Text
Sheet1.Cells(x, "O").Value = Me.ComboBox14.Text
Sheet1.Cells(x, "P").Value = Me.ComboBox15.Text
Sheet1.Cells(x, "Q").Value = Me.ComboBox16.Text
Sheet1.Cells(x, "R").Value = Me.ComboBox17.Text
Sheet1.Cells(x, "S").Value = Me.ComboBox18.Text
Sheet1.Cells(x, "T").Value = Me.ComboBox19.Text
Sheet1.Cells(x, "U").Value = Me.ComboBox20.Text
Sheet1.Cells(x, "V").Value = Me.ComboBox21.Text
Sheet1.Cells(x, "W").Value = Me.ComboBox22.Text
Sheet1.Cells(x, "X").Value = Me.ComboBox23.Text
Sheet1.Cells(x, "Y").Value = Me.ComboBox24.Text
Sheet1.Cells(x, "Z").Value = Me.ComboBox25.Text
Sheet1.Cells(x, "AA").Value = Me.ComboBox26.Text
Sheet1.Cells(x, "AB").Value = Me.ComboBox27.Text
Sheet1.Cells(x, "AC").Value = Me.ComboBox28.Text
Sheet1.Cells(x, "AD").Value = Me.ComboBox29.Text
Sheet1.Cells(x, "AE").Value = Me.ComboBox30.Text
Sheet1.Cells(x, "AF").Value = Me.ComboBox31.Text
Sheet1.Cells(x, "AG").Value = Me.ComboBox32.Text
Sheet1.Cells(x, "AH").Value = Me.ComboBox33.Text
End If
Next

TextBox5 text has the unique ref numbers as ID!

My question is to speed up the progress if possible cos with that way it runs slow?
Thank you

 

[SpillerBD]

All the rows via loop? That's gonna take a long time. You can get some improvement by adding:

Application.ScreenUpdating = False

before the loop begins.

If there are no calculations required during the loop you can turn calculations off with

Application.Calculation = xlCalculationManual

and then back on before the end of the sub with

Application.Calculation =xlCalculationAutomatic

 

[asyamonique]

Thank you very much..

 

[steve the fish]

Dont use a loop to find your value of x. You could use Find or in this example Match:

x = Application.Match(Me.TextBox5.Text, Sheet1.Columns("AI"), 0)
If IsError(x) Then Exit Sub