Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Clear cells in a range per sheet loop (ecel ms office 2000)

    hello, I am stuck on a macro which loops through each sheet and clears the same ranges maybe some can figure out why i get an expected array alert on my ubound??



    Private Sub CommandButton1_Click()
    Dim vClearlist




    vClearlist = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", _
    "Sheet8", "Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13")

    For x = LBound(vcClearlist) + 1 To UBound(vClearlist)
    With Worksheets(vClearlist(x))
    Sheets(vClearlist(x)).Range("B9:C28").ClearContent s
    Sheets(vClearlist(x)).Range("E9:E28").ClearContent s
    Sheets(vClearlist(x)).Range("C2:C6").ClearContents
    End With
    Next

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    1) You apparently *still* don't require variable declaration - see <post:=504,476>post 504,476</post:>.
    2) You declare vClearList as a String.
    2) You use both vClearList and vcClearList.
    3) Why the LBound(...) + 1 ?
    4) You use With ... but don't do anything with it and you don't have a corresponding End With
    5) Sheet(...) is not valid, it should be Sheets(...)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    How many more edits should we expect? <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    I have cleaned up the code with a few varible errors Please ignore the first posts Code

    Private Sub CommandButton1_Click()
    Dim vClearlist
    dim x as integer




    vClearlist = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", _
    "Sheet8", "Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13")

    For x = LBound(vClearlist) + 1 To UBound(vClearlist)
    With Worksheets(vClearlist(x))
    Sheets(x).Range("B9:C28").ClearContents
    Sheets(x).Range("D928").ClearContents
    Sheets(x).Range("C2:C6").ClearContents
    End With
    Next

    End Sub

    this gives a script out of range error??

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    no more edits, my apolgies, I never thought any one was effected???

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    I repeat: why the LBound(...) + 1 ?
    And why don't you do anything with the With ... End With construction?

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    ok, i am guilty of not remebering the option explict for the second time i will make it a habit <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15> .

    I removed the string declared varible
    Lbound i though would move left to right through the array +1
    I thought that if Iuse a loop with would refer to each worksheet in the array...I used the recorder for this part.

    is there an easier way to clear the contents of sheets 2:13 ranges
    b9:C28
    E9:E28
    C2:C6
    ??/

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    Private Sub CommandButton1_Click()
    Dim vClearlist As Variant
    Dim x As Integer

    vClearlist = Array("Sheet2", "Sheet3", "Sheet4", _
    "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", _
    "Sheet10", "Sheet11", "Sheet12", "Sheet13")

    For x = LBound(vClearlist) To UBound(vClearlist)
    With Worksheets(vClearlist(x))
    .Range("B9:C28,E9:E29,C2:C6").ClearContents
    End With
    Next x
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    Thanks Hans, I a have learned a new technique.

    On problem I am getting a script out of range error?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    That means that one of the sheet names in your array is incorrect.

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    there should not be a problem with the names in the
    Array("Sheet2", "Sheet3", "Sheet4", _
    "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", _
    "Sheet10", "Sheet11", "Sheet12", "Sheet13")
    these are the default sheet names

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    They may be the default names, but are they the names of the sheets as displayed in the sheet tabs?

  13. #13
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    nope, they are not

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear cells in a range per sheet loop (ecel ms office 2000)

    That's why you get the error. Workheets("Sheet2") refers to the worksheet whose displayed name is Sheet2. You cannot refer to the codename this way.
    If you know the positions of the sheets in the left-to-right order, you don't need the array, and you can use for example

    For x = 2 To 13
    Worksheets(x).Range(...).ClearContents
    Next x

    If you only know the codenames but not the display names nor their positions, it's a bit more complicated:

    Dim wsh As Worksheet
    For Each wsh In ActiveWorkbook.Worksheets
    For x = LBound(vClearList) To UBound(vClearList)
    If wsh.CodeName = vClearList(x) Then
    wsh.Range(...).ClearContents
    Exit For
    End If
    Next x
    Next wsh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •