Consulting

Results 1 to 3 of 3

Thread: Excel VBA Multiple loops

  1. #1
    VBAX Newbie
    Joined
    Sep 2020
    Posts
    1
    Location

    Excel VBA Multiple loops

    I have a workbook where I am using an array to go through certain sheets to check for null ("") cells that have been made by other VBA formulas.
    I am creating another loop to delete the null value
    I am not knowledgeable enough to create a nesting loop. error I am receiving is "Compile error for without next"
    Assistance would be appreciated.

    thanks

    Sub RemoveBlankCells2()
    Dim strLR2 As String
    Dim q As Long
    Dim Sheetname As String
    Dim c As Range
    Dim Sh As Variant


    Application.EnableEvents = True


    Sheets("Sprinkler").Select
    Range("A1").Select




    For Each Sh In Sheets(Array("Fire", "FA Def", "FA NA", "Sprinkler", "Sprinkler Def", "Sprinkler NA", "Suppression", "Suppression Def", "Suppression NA", "Inspections"))
    Sheetname = Sh.Name
    Sheets(Sheetname).Select
    strLR2 = Worksheets(Sheetname).Range("A" & Rows.Count).End(xlUp).Row







    For q = 2 To strLR2
    If Trim(Range("I" & q).Value) = "" Then Range("I" & q).ClearContents
    Next






    End Sub

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    maybe:
    Sub RemoveBlankCells2()
    Dim q As Long, Sh
    
    Application.EnableEvents = False
    For Each Sh In Sheets(Array("Fire", "FA Def", "FA NA", "Sprinkler", "Sprinkler Def", "Sprinkler NA", "Suppression", "Suppression Def", "Suppression NA", "Inspections"))
      With Sh
        For q = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
          If Trim(.Range("I" & q).Value) = "" Then .Range("I" & q).ClearContents
        Next q
      End With
    Next Sh
    Application.EnableEvents = True
    End Sub
    ?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Not tested

    I used Option Explicit

    If you are not using Worksheet_Change or other events, you don't need to disable them

    Sh is Dim-ed as a Worksheet

    To avoid issues with empty strings, I like to use Len() = 0 as a check (it might be a 0 length string)

    To avoid issues with a non-empty string that consists of spaces, I like to use Trim()


    For number based WS looping, I've found it's easier for me to use .Cells(r, c) instead of something like Range("A" & i)

    To answer your question, look at Next q and Next Sh below to see how a nested loop works

    My thoughts

    Option Explicit
    
    
    Sub RemoveBlankCells2()
        Dim strLR2 As String
        Dim q As Long
        Dim Sh As Worksheet
    
    
        For Each Sh In Sheets(Array("Fire", "FA Def", "FA NA", "Sprinkler", "Sprinkler Def", "Sprinkler NA", "Suppression", "Suppression Def", "Suppression NA", "Inspections"))
            With Sh
                strLR2 = .Range("A" & .Rows.Count).End(xlUp).Row
    
    
                For q = 2 To strLR2
                    If Len(Trim(.Cells(q, 9).Value)) = 0 Then .Cells(q, 9).ClearContents
                Next q
            End With
        Next Sh
    
    
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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