If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 > Loop with multiple conditions

 waylander Registered User 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
 healdem Jaded Developer Join Date: Nov 2004 Location: out on a limb Posts: 12,296
 "" 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 __________________ Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:- http://www.gentlemansride.com/rider/healdem
 waylander Registered User 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
 healdem Jaded Developer Join Date: Nov 2004 Location: out on a limb Posts: 12,296
 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 "") __________________ Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:- http://www.gentlemansride.com/rider/healdem
 waylander Registered User 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
 healdem Jaded Developer Join Date: Nov 2004 Location: out on a limb Posts: 12,296
 doesn't sound right can you post a zipped version of your spreadsheet here with all sensitive data removed __________________ Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:- http://www.gentlemansride.com/rider/healdem
 waylander Registered User Join Date: Apr 2006 Posts: 62
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
 CSP Study Monitoring Tool.zip (172.4 KB, 2 views)
 healdem Jaded Developer Join Date: Nov 2004 Location: out on a limb Posts: 12,296
 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 __________________ Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:- http://www.gentlemansride.com/rider/healdem