[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Sheet selection

Patrick C. Simonds

12/17/2006 6:39:00 PM

Is there a way to amend this code so that it does not actually make the
sheet (in this case September) active?

Sub September_Set()

Sheets("September").Select

Range("D4:F13").Select
Selection.Value = ""
Selection.Offset(0, 26).Value = ""

Range("D15:F25").Select
Selection.Value = ""
Selection.Offset(0, 26).Value = ""

Range("A2").Select

End Sub


4 Answers

Gary''s Student

12/17/2006 7:48:00 PM

0

Avoid Selections and stay where you are:


Sub September_Set()
Dim ws As Worksheet
Set ws = Worksheets("September")
With ws

.Range("D4:F13").Value = ""
.Range("D4:F13").Offset(0, 26).Value = ""


.Range("D15:F25").Value = ""
.Range("D15:F25").Offset(0, 26).Value = ""

End With
End Sub

--
Gary's Student


"Patrick Simonds" wrote:

> Is there a way to amend this code so that it does not actually make the
> sheet (in this case September) active?
>
> Sub September_Set()
>
> Sheets("September").Select
>
> Range("D4:F13").Select
> Selection.Value = ""
> Selection.Offset(0, 26).Value = ""
>
> Range("D15:F25").Select
> Selection.Value = ""
> Selection.Offset(0, 26).Value = ""
>
> Range("A2").Select
>
> End Sub
>
>
>

Tom Ogilvy

12/17/2006 8:13:00 PM

0

Sub September_Set()

With Sheets("September")

With .Range("D4:F13")
.ClearContents
.Offset(0, 26).ClearContents
End With
With .Range("D15:F25")
.ClearContents
.Offset(0, 26).ClearContents
End With
End With
End Sub

--
Regards,
Tom Ogilvy



"Patrick Simonds" <ordnance1@comcast.net> wrote in message
news:%23wk9wqgIHHA.1044@TK2MSFTNGP02.phx.gbl...
> Is there a way to amend this code so that it does not actually make the
> sheet (in this case September) active?
>
> Sub September_Set()
>
> Sheets("September").Select
>
> Range("D4:F13").Select
> Selection.Value = ""
> Selection.Offset(0, 26).Value = ""
>
> Range("D15:F25").Select
> Selection.Value = ""
> Selection.Offset(0, 26).Value = ""
>
> Range("A2").Select
>
> End Sub
>


Patrick C. Simonds

12/18/2006 12:18:00 AM

0

Can the following also be done without selecting the sheet?

If Worksheets("November").Range("O39").Value =
Worksheets("November").Range("W1").Value Then
Worksheets("November").Range("M40").Value = "Thanksgiving"
Worksheets("November").Range("M41").Value = "Day"

Range("A2:C2").Select

End If

If Worksheets("November").Range("O51").Value =
Worksheets("November").Range("W1").Value Then
Worksheets("November").Range("M52").Value = "Thanksgiving"
Worksheets("November").Range("M53").Value = "Day"

Range("A2:C2").Select

End If




"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uQYQ%23fhIHHA.1816@TK2MSFTNGP06.phx.gbl...
> Sub September_Set()
>
> With Sheets("September")
>
> With .Range("D4:F13")
> .ClearContents
> .Offset(0, 26).ClearContents
> End With
> With .Range("D15:F25")
> .ClearContents
> .Offset(0, 26).ClearContents
> End With
> End With
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Patrick Simonds" <ordnance1@comcast.net> wrote in message
> news:%23wk9wqgIHHA.1044@TK2MSFTNGP02.phx.gbl...
>> Is there a way to amend this code so that it does not actually make the
>> sheet (in this case September) active?
>>
>> Sub September_Set()
>>
>> Sheets("September").Select
>>
>> Range("D4:F13").Select
>> Selection.Value = ""
>> Selection.Offset(0, 26).Value = ""
>>
>> Range("D15:F25").Select
>> Selection.Value = ""
>> Selection.Offset(0, 26).Value = ""
>>
>> Range("A2").Select
>>
>> End Sub
>>
>
>


Tom Ogilvy

12/18/2006 12:36:00 AM

0

the selecting in that code is superfluous

If Worksheets("November").Range("O39").Value = _
Worksheets("November").Range("W1").Value Then
Worksheets("November").Range("M40").Value = "Thanksgiving"
Worksheets("November").Range("M41").Value = "Day"
End If

If Worksheets("November").Range("O51").Value = _
Worksheets("November").Range("W1").Value Then
Worksheets("November").Range("M52").Value = "Thanksgiving"
Worksheets("November").Range("M53").Value = "Day"
End If

should work fine.

--
Regards,
Tom Ogilvy


"Patrick Simonds" <ordnance1@comcast.net> wrote in message
news:Oxi74njIHHA.3872@TK2MSFTNGP06.phx.gbl...
> Can the following also be done without selecting the sheet?
>
> If Worksheets("November").Range("O39").Value =
> Worksheets("November").Range("W1").Value Then
> Worksheets("November").Range("M40").Value = "Thanksgiving"
> Worksheets("November").Range("M41").Value = "Day"
>
> Range("A2:C2").Select
>
> End If
>
> If Worksheets("November").Range("O51").Value =
> Worksheets("November").Range("W1").Value Then
> Worksheets("November").Range("M52").Value = "Thanksgiving"
> Worksheets("November").Range("M53").Value = "Day"
>
> Range("A2:C2").Select
>
> End If
>
>
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:uQYQ%23fhIHHA.1816@TK2MSFTNGP06.phx.gbl...
>> Sub September_Set()
>>
>> With Sheets("September")
>>
>> With .Range("D4:F13")
>> .ClearContents
>> .Offset(0, 26).ClearContents
>> End With
>> With .Range("D15:F25")
>> .ClearContents
>> .Offset(0, 26).ClearContents
>> End With
>> End With
>> End Sub
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>>
>> "Patrick Simonds" <ordnance1@comcast.net> wrote in message
>> news:%23wk9wqgIHHA.1044@TK2MSFTNGP02.phx.gbl...
>>> Is there a way to amend this code so that it does not actually make the
>>> sheet (in this case September) active?
>>>
>>> Sub September_Set()
>>>
>>> Sheets("September").Select
>>>
>>> Range("D4:F13").Select
>>> Selection.Value = ""
>>> Selection.Offset(0, 26).Value = ""
>>>
>>> Range("D15:F25").Select
>>> Selection.Value = ""
>>> Selection.Offset(0, 26).Value = ""
>>>
>>> Range("A2").Select
>>>
>>> End Sub
>>>
>>
>>
>
>