Help to decrease processiong time vba

[VISHAL120]

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 fpr further calculation.
can i be guided on how i can increase its performance.

many thanks in advance.

[Simon Lloyd]

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]

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]

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]

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]

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