PDA

View Full Version : help to decrease processiong time vba



VISHAL120
09-23-2011, 07:44 AM
Hi All,

I have the following codes working which takes approx. 11 secs to process and which seems very long.

start_time = Time
ROWSTART = MADA_HEADER_ROW
ROWEND = Range("A65536").Offset(0, Mada_POs_Column - 1).End(xlUp).Row


ROWWORKING = ROWSTART

Do While ROWWORKING <= ROWEND


'Call wait_show_madaloading
'DoEvents
Do While Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_POs_Column - 1) <> ""

Application.StatusBar = " Updating Leadtime by Line " & (ROWWORKING) & " of " & ROWEND & " Percentage updated: " & Format((ROWWORKING - ROWSTART) / (ROWEND - ROWSTART), "0 %") & " completed"
'frmWait.Label1 = Format((ROWWORKING - ROWSTART) / (ROWEND - ROWSTART), "0 %") & " completed"

'ASSIGN DEPT ON VARIABLE
KNIT_DEPT = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_Knit_Column - 1)
MUP_DEPT = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_Dept_MUP_Column - 1)
DH_DEPT = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_DH_Column - 1)
TECH_DEPT = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_TECH_Column - 1)
second_MUp_Dept = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2ndMUP_column - 1)
SECOND_DH_DEPT = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2nd_DH_column - 1)
SECOND_TECH_DEPT = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2nd_Tech_Column - 1)
EMB_DEPT = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_Emb_Column - 1)
PRNT_DEPT = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_PRNT_Column - 1)
FINAL_DEPT = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_Dept_Final_Column - 1)
PACK_DEPT = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_PAck_Column - 1)

On Error Resume Next


Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_Knit_Column - 1) _
= WorksheetFunction.VLookup(KNIT_DEPT, Range("Transpose_Route_Type!Rt_transpose_Data_Clear"), Trans_RTE_LEadtime_Column, False)

Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_Dept_MUP_Column - 1) _
= WorksheetFunction.VLookup(MUP_DEPT, Range("Transpose_Route_Type!Rt_transpose_Data_Clear"), Trans_RTE_LEadtime_Column, False)

Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_DH_Column - 1) _
= WorksheetFunction.VLookup(DH_DEPT, Range("Transpose_Route_Type!Rt_transpose_Data_Clear"), Trans_RTE_LEadtime_Column, False)

Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_TECH_Column - 1) _
= WorksheetFunction.VLookup(TECH_DEPT, Range("Transpose_Route_Type!Rt_transpose_Data_Clear"), Trans_RTE_LEadtime_Column, False)

Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2ndMUP_column - 1) _
= WorksheetFunction.VLookup(second_MUp_Dept, Range("Transpose_Route_Type!Rt_transpose_Data_Clear"), Trans_RTE_LEadtime_Column, False)

Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2nd_DH_column - 1) _
= WorksheetFunction.VLookup(SECOND_DH_DEPT, Range("Transpose_Route_Type!Rt_transpose_Data_Clear"), Trans_RTE_LEadtime_Column, False)

Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2nd_Tech_Column - 1) _
= WorksheetFunction.VLookup(SECOND_TECH_DEPT, Range("Transpose_Route_Type!Rt_transpose_Data_Clear"), Trans_RTE_LEadtime_Column, False)

Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_Emb_Column - 1) _
= WorksheetFunction.VLookup(EMB_DEPT, Range("Transpose_Route_Type!Rt_transpose_Data_Clear"), Trans_RTE_LEadtime_Column, False)

Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_PRNT_Column - 1) _
= WorksheetFunction.VLookup(PRNT_DEPT, Range("Transpose_Route_Type!Rt_transpose_Data_Clear"), Trans_RTE_LEadtime_Column, False)

Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_Dept_Final_Column - 1) _
= WorksheetFunction.VLookup(FINAL_DEPT, Range("Transpose_Route_Type!Rt_transpose_Data_Clear"), Trans_RTE_LEadtime_Column, False)

Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_PAck_Column - 1) _
= WorksheetFunction.VLookup(PACK_DEPT, Range("Transpose_Route_Type!Rt_transpose_Data_Clear"), Trans_RTE_LEadtime_Column, False)



'CHECK IS VALUES IS NUMERIC OPR NOT AND IF NOT CHANGE IT TO O FOR CALCULATION

'========================================================================== ========================
' KNIT_DEPT_1 = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_Knit_Column - 1)

If IsNumeric(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_Knit_Column - 1)) Then
Else
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_Knit_Column - 1) = 0
End If
'========================================================================== ========================
'MUP_DEPT_1 = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_Dept_MUP_Column - 1)

If IsNumeric(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_Dept_MUP_Column - 1)) Then
Else
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_Dept_MUP_Column - 1) = 0
End If
'========================================================================== ========================
' DH_DEPT_1 = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_DH_Column - 1)

If IsNumeric(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_DH_Column - 1)) Then
Else
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_DH_Column - 1) = 0
End If
'========================================================================== ========================
'TECH_DEPT_1 = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_TECH_Column - 1)

If IsNumeric(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_TECH_Column - 1)) Then
Else
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_TECH_Column - 1) = 0
End If
'========================================================================== ========================
'second_MUp_Dept_1 = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2ndMUP_column - 1)

If IsNumeric(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2ndMUP_column - 1)) Then
Else
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2ndMUP_column - 1) = 0
End If
'========================================================================== ========================
' SECOND_DH_DEPT_1 = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2nd_DH_column - 1)

If IsNumeric(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2nd_DH_column - 1)) Then
Else
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2nd_DH_column - 1) = 0
End If
'========================================================================== ========================
'SECOND_TECH_DEPT_1 = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2nd_Tech_Column - 1)

If IsNumeric(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2nd_Tech_Column - 1)) Then
Else
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_2nd_Tech_Column - 1) = 0
End If
'========================================================================== ========================
'EMB_DEPT_1 = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_Emb_Column - 1)

If IsNumeric(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_Emb_Column - 1)) Then
Else
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_Emb_Column - 1) = 0
End If
'========================================================================== ========================
' PRNT_DEPT_1 = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_PRNT_Column - 1)

If IsNumeric(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_PRNT_Column - 1)) Then
Else
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_PRNT_Column - 1) = 0
End If
'========================================================================== ========================
'FINAL_DEPT_1 = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_Dept_Final_Column - 1)

If IsNumeric(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_Dept_Final_Column - 1)) Then
Else
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_Dept_Final_Column - 1) = 0
End If
'========================================================================== ========================
' PACK_DEPT_1 = Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_PAck_Column - 1)

If IsNumeric(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_PAck_Column - 1)) Then
Else
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_Dept_PAck_Column - 1) = 0
End If

'CALCULATING END DATES FOR EACH DEPT.
'END DATE MUP
Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, MAda_End_Mup_Date_Column - 1) _
= WorksheetFunction.Sum(Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_Dept_MUP_Column - 1) _
+ Range("Mada_Loading!A1").Offset(MADA_HEADER_ROW - 1, Mada_End_Knit_Column - 1))



MADA_HEADER_ROW = MADA_HEADER_ROW + 1

ROWWORKING = ROWWORKING + 1
Loop
'ROWWORKING = ROWWORKING + 1
Loop

end_time = Time
time_string = Format(end_time - start_time, "ss")
Application.StatusBar = " Factory Loading Data Updated in " & time_string & "Secs"

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Cursor = xlDefault
the codes actually make a vlookup and then if there is error it convert the error to 0 fopr further calculation.

can i be guided on how i can increase its performance.

many thanks in advance.

Simon Lloyd
09-23-2011, 09:34 PM
Get rid of all that checking and changing to zero, change your VLOOKUPs for IF(VLOOKUP = 0 then show 0

Get rid of all those variables for ranges and create dynamic named ranges!

Jan Karel Pieterse
09-23-2011, 11:06 PM
Looks like no code is needed and just formulas would do.
Example:
=IF(ISERROR(VLookup(KNIT_DEPT, Transpose_Route_Type!Rt_transpose_Data_Clear, Trans_RTE_LEadtime_Column, False)),0,VLookup(KNIT_DEPT, Transpose_Route_Type!Rt_transpose_Data_Clear, Trans_RTE_LEadtime_Column, False))

VISHAL120
09-24-2011, 01:41 AM
hi thanks for the reply will try and revert on that. concerning formula, i can't use becuase every time the data changes somentimes more and less.

thanks anyway

Jan Karel Pieterse
09-24-2011, 08:47 AM
But you can write a relatively simple macro that applies the formula to the new range. You can apply a formula to a whole range of cells in one go:

Range("A1:A1000").Formula = "=VLOOKUP(B1,Sheet2!$A$1:$C$10000,3,FALSE)"

Simon Lloyd
09-24-2011, 11:09 AM
If you use Dynamic Named ranges they will shrink or expand as data is added or removed. Take a look here for a well worked "How to...!" http://www.contextures.com/xlnames01.html#Dynamic