PDA

View Full Version : [SOLVED] Shorten code



Juriemagic
06-04-2015, 01:59 AM
Hi good people!

I have a lengthy code and Excel thinks a bit while executing. I'm trying to get the downtime from 3 sec down to at least 1 sec. Could this code be re-written so that it would execute a bit faster?. Please help...

If Not Range("D3") Is Nothing Then
Sheets("Update Room").Unprotect
If Range("AI1") = 3 Then
Range("I8").Locked = True
Else
Range("I8").Locked = False
End If
If Range("AI2") = 3 Then
Range("I10").Locked = True
Else
Range("I10").Locked = False
End If
If Range("AI3") = 3 Then
Range("I12").Locked = True
Else
Range("I12").Locked = False
End If
If Range("AI4") = 3 Then
Range("K8").Locked = True
Else
Range("K8").Locked = False
End If
If Range("AI5") = 3 Then
Range("K10").Locked = True
Else
Range("K10").Locked = False
End If
If Range("AI6") = 3 Then
Range("K12").Locked = True
Else
Range("K12").Locked = False
End If
If Range("AI7") = 3 Then
Range("K14").Locked = True
Else
Range("K14").Locked = False
End If
If Range("AI8") = 3 Then
Range("K16").Locked = True
Else
Range("K16").Locked = False
End If
If Range("AI9") = 3 Then
Range("K18").Locked = True
Else
Range("K18").Locked = False
End If
If Range("AI10") = 3 Then
Range("K20").Locked = True
Else
Range("K20").Locked = False
End If
If Range("AI11") = 3 Then
Range("K22").Locked = True
Else
Range("K22").Locked = False
End If
If Range("AI12") = 3 Then
Range("K24").Locked = True
Else
Range("K24").Locked = False
End If
If Range("AI13") = 3 Then
Range("K26").Locked = True
Else
Range("K26").Locked = False
End If
If Range("AI14") = 3 Then
Range("K28").Locked = True
Else
Range("K28").Locked = False
End If
If Range("AI15") = 3 Then
Range("K30").Locked = True
Else
Range("K30").Locked = False
End If
If Range("AI16") = 3 Then
Range("M8").Locked = True
Else
Range("M8").Locked = False
End If
If Range("AI17") = 3 Then
Range("M10").Locked = True
Else
Range("M10").Locked = False
End If
If Range("AI18") = 3 Then
Range("M12").Locked = True
Else
Range("M12").Locked = False
End If
If Range("AI19") = 3 Then
Range("M14").Locked = True
Else
Range("M14").Locked = False
End If
If Range("AI20") = 3 Then
Range("O8").Locked = True
Else
Range("O8").Locked = False
End If
If Range("AI21") = 3 Then
Range("O10").Locked = True
Else
Range("O10").Locked = False
End If
If Range("AI22") = 3 Then
Range("O12").Locked = True
Else
Range("O12").Locked = False
End If
If Range("AI23") = 3 Then
Range("Q8").Locked = True
Else
Range("Q8").Locked = False
End If
If Range("AI24") = 3 Then
Range("Q10").Locked = True
Else
Range("Q10").Locked = False
End If
If Range("AI25") = 3 Then
Range("Q12").Locked = True
Else
Range("Q12").Locked = False
End If
If Range("AI26") = 3 Then
Range("S8").Locked = True
Else
Range("S8").Locked = False
End If
If Range("AI27") = 3 Then
Range("S10").Locked = True
Else
Range("S10").Locked = False
End If
If Range("AI28") = 3 Then
Range("S12").Locked = True
Else
Range("S12").Locked = False
End If
If Range("AI29") = 3 Then
Range("U8").Locked = True
Else
Range("U8").Locked = False
End If
If Range("AI30") = 3 Then
Range("U10").Locked = True
Else
Range("U10").Locked = False
End If
If Range("AI31") = 3 Then
Range("U12").Locked = True
Else
Range("U12").Locked = False
End If
If Range("AI32") = 3 Then
Range("U14").Locked = True
Else
Range("U14").Locked = False
End If
If Range("AI33") = 3 Then
Range("W8").Locked = True
Else
Range("W8").Locked = False
End If
If Range("AI34") = 3 Then
Range("W10").Locked = True
Else
Range("W10").Locked = False
End If
If Range("AI35") = 3 Then
Range("W12").Locked = True
Else
Range("W12").Locked = False
End If
If Range("AI36") = 3 Then
Range("W14").Locked = True
Else
Range("W14").Locked = False
End If
If Range("AI37") = 3 Then
Range("W16").Locked = True
Else
Range("W16").Locked = False
End If
If Range("AI38") = 3 Then
Range("W18").Locked = True
Else
Range("W18").Locked = False
End If
If Range("AI39") = 3 Then
Range("W20").Locked = True
Else
Range("W20").Locked = False
End If
If Range("AI40") = 3 Then
Range("W22").Locked = True
Else
Range("W22").Locked = False
End If
If Range("AI41") = 3 Then
Range("W24").Locked = True
Else
Range("W24").Locked = False
End If
If Range("AI42") = 3 Then
Range("W26").Locked = True
Else
Range("W26").Locked = False
End If
If Range("AI43") = 3 Then
Range("W28").Locked = True
Else
Range("W28").Locked = False
End If
If Range("AI44") = 3 Then
Range("W30").Locked = True
Else
Range("W30").Locked = False
End If
If Range("AI45") = 3 Then
Range("W32").Locked = True
Else
Range("W32").Locked = False
End If
Sheets("Update Room").Protect
End If
End Sub

jonh
06-04-2015, 03:45 AM
Hello.


Sub test()

Dim S, n, a, i, l

S = "I8,I10,I12,K8,K10,K12,K14,K16,K18,K20,K22,K24," & _
"K26,K28,K30,M8,M10,M12,M14,O8,O10,O12,Q8,Q10,Q12," & _
"S8,S10,S12,U8,U10,U12,U14,W8,W10,W12,W14,W16,W18," & _
"W20,W22,W24,W26,W28,W30,W32"
a = Split(S, ",")
For i = 0 To UBound(a)
If Range("AI" & i + 1) = 3 Then
If Len(l) Then l = l & ","
l = l & a(i)
Else
If Len(n) Then n = n & ","
n = n & a(i)
End If
Next
Range(n).Locked = False
Range(l).Locked = True
End Sub

Juriemagic
06-04-2015, 05:17 AM
hello Jonh,

I get an error
method'Range'of object'_Worksheet'failed

In the code the part that says Range(1).Locked=True is highlighted in yellow...

Aflatoon
06-04-2015, 05:28 AM
It's Range(L) not Range(1) - did you copy and paste the code?

Note: if all, or none, of the cells in AI contain 3, you will get an error, so you should test the strings first:

If len(n) <> 0 then Range(n).Locked = False
If len(l) <> 0 then Range(l).Locked = True

Juriemagic
06-04-2015, 05:43 AM
Yes I copied and pasted. It looked like a "1", I checked with the keyboard and noticed it was a small (L)..sorry about that. Your correction did the trick...wow!!!..amazing!!. I appreciate your help more than you will know, thank you very very much...have a blessed day!

snb
06-04-2015, 06:22 AM
removed the code