Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: Only run loop if cell is blank in VB

    please look at the following code:

    For kLoop = 6 To (iLoop - 1) Step 1

    If xlSheet.Range("A" & kLoop & ":A" & kLoop).Value = tCLRNid _
    And xlSheet.Range("C" & kLoop & ":C" & kLoop).Value = tCLRNLocation _
    And xlSheet.Range("H" & kLoop & ":H" & kLoop).Value = "Checks Complete" And tCounter = 0 Then

    xlSheetThis.Range("U" & MasterLoop & ":U" & MasterLoop).Value = _
    Format(xlSheet.Range("I" & kLoop & ":I" & kLoop).Value, "mm/dd/yyyy")

    tEnd = Format(xlSheet.Range("I" & kLoop & ":I" & kLoop).Value, "dd/mm/yyyy")
    tCounter = tCounter + 1


    End If

    ===========================================

    can anyone please tell me how to modify the code so the Loop will only be applied to cells in Column "U" if it is blank, meaning that a value will be returned to column "U" only if the cell is blank and not overwrite an existing value

    your help will be greatly appreciated

  2. #2
    Join Date
    Apr 2009
    Posts
    14
    You just need an extra condition...
    Code:
    For kLoop = 6 To (iLoop - 1) Step 1
    
    If xlSheet.Range("A" & kLoop).Value = tCLRNid _
        And xlSheet.Range("C" & kLoop).Value = tCLRNLocation _
        And xlSheet.Range("H" & kLoop).Value = "Checks Complete" _
        And IsEmpty(xlSheetThis.Range("U" & MasterLoop)) _
        And tCounter = 0 Then
    
        xlSheetThis.Range("U" & MasterLoop).Value = _
            Format(xlSheet.Range("I" & kLoop).Value, "mm/dd/yyyy")
    
        tEnd = Format(xlSheet.Range("I" & kLoop).Value, "dd/mm/yyyy")
        tCounter = tCounter + 1
    
    
    End If
    
    Next

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    Hi Kafrin,
    i realised my mistake now,
    many thanks for that its a big help

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    If you post the code within the MasterLoop and give a general explanation about what it does, we might be able to touch it up a bit for you?

Posting Permissions

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