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

"" 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

Hi thanks for the reply,
yes your answer to condition 3 is correct, I am not sure how to apply the first 2

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 "")

Hi,
this appears to only include values if N and O are both NULL but excludes if BOTH Not Null

doesn't sound right
can you post a zipped version of your spreadsheet here with all sensitive data removed

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()

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
seems to fix it

it works a treat,
thank you so much for your support.