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.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > Visual Basic > Loop with multiple conditions

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
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
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,079
"" 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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old
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
Reply With Quote
  #4 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,079
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #5 (permalink)  
Old
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
Reply With Quote
  #6 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,079
doesn't sound right
can you post a zipped version of your spreadsheet here with all sensitive data removed
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
File Type: zip CSP Study Monitoring Tool.zip (172.4 KB, 2 views)
Reply With Quote
  #8 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,079
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Apr 2006
Posts: 62
it works a treat,
thank you so much for your support.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On