# Thread: Loop with multiple conditions

1. Registered User
Join Date
Apr 2006
Posts
65

## Unanswered: 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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
"" 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

3. Registered User
Join Date
Apr 2006
Posts
65
yes your answer to condition 3 is correct, I am not sure how to apply the first 2

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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 "")

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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
doesn't sound right
can you post a zipped version of your spreadsheet here with all sensitive data removed

7. Registered User
Join Date
Apr 2006
Posts
65
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()

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

9. Registered User
Join Date
Apr 2006
Posts
65
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
•