Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ned to speed up my code
Greetings all.
Windows xp xl97 This code is way too slow. Any help would be appreciated. -I have a user form that creates an invoice. When they enter the product number into the form, if it is not found in the list,another form appears. -This form allows them to enter the product description and code. -There is a button on the sub form that allows the user to search the current parts list for similar descriptions to get the appropriate code to use. The code attached is located on the third form. They enter the description to search for and hit the cmdSearch button. It works but is too slow. Thanks in advance, -doodle Private Sub cmdSearch_Click() On Error GoTo errClear Dim mysrc As Integer '# used in status bar so i can see progress Dim i As String mysrc = 6 i = frmPartsList.txtSearchCriteria.Text Application.ScreenUpdating = False Application.StatusBar = "Searching Row # " & mysrc 'Clears previous temp table Range("GO6:GQ65536").ClearContents Select Case cmbSearchBy Case Is = "Product Description" 'If searching by desription Sheets("Data").Select With Worksheets("Data").Range("gm6:gm65536") 'with descrip column Set c = .Find(What:=i, LookIn:=xlValues, lookat:=xlPart) 'find search text If Not c Is Nothing Then firstAddress = c.Address Do 'Keep looking until found all mysrc = mysrc + 1 'Update my status bar Application.StatusBar = "Searching Row # " & mysrc ' Update my temp table with results Set myRow = Sheets("Data").Range("GO65536").End(xlUp) myRow.Offset(1, 0).Value = Range(c.Address).Offset(0, -2).Text myRow.Offset(1, 1).Value = Range(c.Address).Offset(0, -1).Text myRow.Offset(1, 2).Value = Range(c.Address).Text Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Case "Part Number" 'same as code above except looking at number column Sheets("Data").Select With Worksheets("Data").Range("gk6:gk65536") Set c = .Find(What:=i, LookIn:=xlValues, lookat:=xlPart) If Not c Is Nothing Then firstAddress = c.Address Do mysrc = mysrc + 1 Application.StatusBar = "Searching Row # " & mysrc Set myRow = Sheets("Data").Range("GO65536").End(xlUp) myRow.Offset(1, 2).Value = Range(c.Address).Offset(0, 2).Text myRow.Offset(1, 1).Value = Range(c.Address).Offset(0, 1).Text myRow.Offset(1, 0).Value = Range(c.Address).Text Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Select If c Is Nothing Then MsgBox Prompt:="There are no parts listed" & _ " that match your search.", Buttons:=vbOKOnly If Not c Is Nothing Then frmPartsList.lstParts.Visible = True If Not c Is Nothing Then frmPartsList.lblPartNum.Visible = True If Not c Is Nothing Then frmPartsList.lblPartNum2.Visible = True If Not c Is Nothing Then frmPartsList.lblHCode.Visible = True If Not c Is Nothing Then frmPartsList.lblHCode2.Visible = True If Not c Is Nothing Then frmPartsList.lblPart.Visible = True Sheets("FrontPage").Select Application.ScreenUpdating = True Application.StatusBar = False Exit Sub errClear: Sheets("FrontPage").Select Application.ScreenUpdating = True Application.StatusBar = False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ned to speed up my code
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ned to speed up my code
Don,
Thanks for the tip. The table is 40k rows currently. I made the change anyway. No chnage in performance. Have any other ideas? -doodle |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ned to speed up my code
Although excel is very good, you might want to think about moving over to
something designed better for large amount of data, like for instance a database e.g Access. For the amount of infomaation you are ploughing through it will be slow, but a few tweaks might help (but not much) e.g simplify the if statements If (c Is Nothing) Then MsgBox Prompt:="There are no parts listed that match your search.", buttons:=vbOKOnly else If (Not c Is Nothing) Then frmPartsList.lstParts.Visible = True frmPartsList.lblPartNum.Visible = True frmPartsList.lblPartNum2.Visible = True frmPartsList.lblHCode.Visible = True frmPartsList.lblHCode2.Visible = True frmPartsList.lblPart.Visible = True end if also do the search for both parts at same time not one after the other. By doing it the way you have you have just doubled the time required. Also look at the assignments for the code, justs seems very long winded. wouldnt a simple text and number check against cell values be easier and overall quicker. e.g dim myrange as range myrange=worksheets("data").cells(rows.count,"gm"). end(xlup).row for each cell in myrange etc..... etc... look for the simplest way is normally the best. Anthony wrote in message oups.com... Don, Thanks for the tip. The table is 40k rows currently. I made the change anyway. No chnage in performance. Have any other ideas? -doodle |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ned to speed up my code
Hi Anthony - thanks for your response.
I agree that it would be much easier for me to do this in Access. Especially since that is the program I normally code in. Unfortunately, (and you can probably tell since I am using xl97), the department I am building this for does not want me to use access because their employees are not trained on it and many of them do not have access loaded on their machines. I suppose I could simplify my statements a bit. I will try that. I cannot do a number vs. text statement to judge between part number and part description because the part numbers have text in them also. Thanks again, -doodle Anthony wrote: Although excel is very good, you might want to think about moving over to something designed better for large amount of data, like for instance a database e.g Access. For the amount of infomaation you are ploughing through it will be slow, but a few tweaks might help (but not much) e.g simplify the if statements If (c Is Nothing) Then MsgBox Prompt:="There are no parts listed that match your search.", buttons:=vbOKOnly else If (Not c Is Nothing) Then frmPartsList.lstParts.Visible = True frmPartsList.lblPartNum.Visible = True frmPartsList.lblPartNum2.Visible = True frmPartsList.lblHCode.Visible = True frmPartsList.lblHCode2.Visible = True frmPartsList.lblPart.Visible = True end if also do the search for both parts at same time not one after the other. By doing it the way you have you have just doubled the time required. Also look at the assignments for the code, justs seems very long winded. wouldnt a simple text and number check against cell values be easier and overall quicker. e.g dim myrange as range myrange=worksheets("data").cells(rows.count,"gm"). end(xlup).row for each cell in myrange etc..... etc... look for the simplest way is normally the best. Anthony wrote in message oups.com... Don, Thanks for the tip. The table is 40k rows currently. I made the change anyway. No chnage in performance. Have any other ideas? -doodle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Speed Up A Code | Excel Worksheet Functions | |||
Speed up Code? | Excel Programming | |||
Speed up code | Excel Programming | |||
Code Speed Up | Excel Programming |