Prev: Selecting Multiple Controls
Next: Removing artifacts from a spreadsheet with VBA
From: ordnance1 on 3 Apr 2010 15:37
Not sure why it runs slow. I thought the Application.ScreenUpdating =
False would do the trick.

Sub Holidays()

Application.ScreenUpdating = False

'New Years Day

With Sheets("January")

If Range("C4") = Range("R2") Then
Range("B27").Value = "New Years"
Range("B27").HorizontalAlignment = xlCenter
Range("B30").Value = "Day"
Range("B30").HorizontalAlignment = xlCenter
Range("A3").Select

ElseIf Range("B27") = "New Years" Then

Range("B27").Value = ""
Range("B27").HorizontalAlignment = xlLeft
Range("B27").IndentLevel = 1
Range("B30").Value = ""
Range("B30").HorizontalAlignment = xlLeft
Range("B30").IndentLevel = 1
Range("A3").Select

End If

If Range("E4") = Range("R2") Then
Range("D27").Value = "New Years"
Range("D27").HorizontalAlignment = xlCenter
Range("D30").Value = "Day"
Range("D30").HorizontalAlignment = xlCenter
Range("A3").Select

ElseIf Range("D27") = "New Years" Then

Range("D27").Value = ""
Range("D27").HorizontalAlignment = xlLeft
Range("D27").IndentLevel = 1
Range("D30").Value = ""
Range("D30").HorizontalAlignment = xlLeft
Range("D30").IndentLevel = 1
Range("A3").Select

End If

End With

Application.ScreenUpdating = True

End Sub


From: Paul on 3 Apr 2010 15:49

You're using a With/EndWith, but you're not using it. You should have
a "." in front of every Range statement (or other property associated
with the With object). See some updated code below, hopefully it runs
faster.



Code:
--------------------



Sub Holidays()
Application.ScreenUpdating = False

With Sheets("January")
If .Range("C4") = .Range("R2") Then
.Range("B27").Value = "New Years"
.Range("B27").HorizontalAlignment = xlCenter
.Range("B30").Value = "Day"
.Range("B30").HorizontalAlignment = xlCenter
ElseIf .Range("B27") = "New Years" Then
.Range("B27").Value = ""
.Range("B27").HorizontalAlignment = xlLeft
.Range("B27").IndentLevel = 1
.Range("B30").Value = ""
.Range("B30").HorizontalAlignment = xlLeft
.Range("B30").IndentLevel = 1
End If

If .Range("E4") = .Range("R2") Then
.Range("D27").Value = "New Years"
.Range("D27").HorizontalAlignment = xlCenter
.Range("D30").Value = "Day"
.Range("D30").HorizontalAlignment = xlCenter
ElseIf .Range("D27") = "New Years" Then
.Range("D27").Value = ""
.Range("D27").HorizontalAlignment = xlLeft
.Range("D27").IndentLevel = 1
.Range("D30").Value = ""
.Range("D30").HorizontalAlignment = xlLeft
.Range("D30").IndentLevel = 1
End If
.Range("A3").Select
End With
Application.ScreenUpdating = True
End Sub

--------------------


--
Paul

- Paul
------------------------------------------------------------------------
Paul's Profile: 1697
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192721

http://www.thecodecage.com/forumz

 | 
Pages: 1
Prev: Selecting Multiple Controls
Next: Removing artifacts from a spreadsheet with VBA