Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2006
    Posts
    62

    Loop with multiple conditions

    Hi,
    i am extracting a range of values from one excel sheet to another using the for loop and I have come across a complexity to which I will be most grateful for any help.

    The conditions that need to be applied to the data to be extracted is

    Column K = Not Null
    Column Q = Null
    If Column N and O are both Null (include in loop)
    If Column N and O are both Not Null (include in loop)
    If Column N is not Null AND O is Null (exclude from loop)

    I am stumped on how to tackle column N and O, any help will be most appreciated:


    For iMasterLoop = 2 To (iLoop - 1) Step 1

    If xlMainSheet.Range("K" & iMasterLoop & ":K" & iMasterLoop).Value <> "" And _
    xlMainSheet.Range("Q" & iMasterLoop & ":Q" & iMasterLoop).Value = "" Then

    xlLocalClock.Range("A" & gCount & ":A" & gCount).Value = xlMainSheet.Range("A" & iMasterLoop & ":A" & iMasterLoop).Value
    xlLocalClock.Range("B" & gCount & ":B" & gCount).Value = xlMainSheet.Range("B" & iMasterLoop & ":B" & iMasterLoop).Value
    xlLocalClock.Range("C" & gCount & ":C" & gCount).Value = xlMainSheet.Range("C" & iMasterLoop & ":C" & iMasterLoop).Value
    xlLocalClock.Range("D" & gCount & "" & gCount).Value = xlMainSheet.Range("K" & iMasterLoop & ":K" & iMasterLoop).Value
    xlLocalClock.Range("E" & gCount & ":E" & gCount).Value = (DateDiff("d", xlMainSheet.Range("K" & iMasterLoop & ":K" & iMasterLoop).Value, Now() - xlMainSheet.Range("O" & iMasterLoop & ":O" & iMasterLoop)))


    gCount = gCount + 1

    End If

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,446
    "" isnt the same as NULL
    to test for NULL use the ISNULL function
    condition 1 & 2 are easy to satisfy
    if the return values from isnull match then process the row
    however isn't condition 3 already covered by 1 & 2 ie if Columns N & O are both NULL oir both have values you are processing the row. if they do not match you are not processing the row


    incidentally this is an EXCEL question NOT a VB question, so you'd usually be better of asking
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2006
    Posts
    62
    Hi thanks for the reply,
    yes your answer to condition 3 is correct, I am not sure how to apply the first 2

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,446
    if ISNULL(NCellRef) = isnull(OCellRef) then

    replace the N/OCellRef with your code to the relevent cells
    this shoudl work as it checks to see if the cells are either BOTH NULL or BOTH not NULL by seeing if the return from the ISNULL function is the same

    however you woudl need to check if you also need to verify if "" is different to NULL in EXCEL
    if that is the case then you have an additional area of complication
    N is either NULL or "" AND O is either NULL or ""
    OR
    (N is neither NULL nor "" AND O is either NULL or ""
    OR N is either NULL nor "" AND O is neither NULL nor "")
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2006
    Posts
    62
    Hi,
    this appears to only include values if N and O are both NULL but excludes if BOTH Not Null

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,446
    doesn't sound right
    can you post a zipped version of your spreadsheet here with all sensitive data removed
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2006
    Posts
    62
    Hi Please see attached,
    sorry the columns in question are M and N on Sheet 1 (not N and O),
    The data is transferred to Sheet 5
    please refer to Module 3 - Transfer_Local_Checks_Runnning()
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,446
    your problem is that you specified NULL (a term that has a very specific meaning especially on a DB site) whereas your values from the sporeadsheet are NOT NULL but set to ""

    changing the if statement to
    Code:
    If (IsNull(xlMainSheet.Range("M" & iMasterLoop & ":M" & iMasterLoop).Value) Or xlMainSheet.Range("M" & iMasterLoop & ":M" & iMasterLoop).Value = "") = (IsNull(xlMainSheet.Range("N" & iMasterLoop & ":N" & iMasterLoop).Value) Or xlMainSheet.Range("N" & iMasterLoop & ":N" & iMasterLoop).Value = "") Then
    seems to fix it
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2006
    Posts
    62
    it works a treat,
    thank you so much for your support.

Posting Permissions

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