Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #5  
Old 07-16-2014, 05:00 AM
BobBridges's Avatar
BobBridges BobBridges is offline Populate Cells with info contained in other cells Windows 7 64bit Populate Cells with info contained in other cells Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

The reason I did the second loop backward is easy to miss at first. Consider what happens if we do the loop the normal way, like this:
Code:
For jr = 1 to 6
  If ows.Cells(jr, 7).Value = "Phase" then ows.Rows(jr).Delete
  Next jr
Let's say that rows 2, 3 and 5 are "Phase" records. So let's trace what the program does:

1) jr is set to 1
2) Checks G1; it's not "Phase", so it skips to the next record
3) jr is now 2
4) Checks G2; it's equal to "Phase", so it deletes row 2.
5) jr is now 3.
6) Checks G3...

But wait! We just deleted row 2; so what was row 3 a moment ago is row 2 now, and row 4 is now row 3, and so on. So when I check row 3 (because jr is now 3), I skipped a row. G3—what was originally G3 but is now G2—has "Phase", but the above logic never looks at it.

There's more than one way to get around that. My way is usually to run the loop backward, like this:
Code:
For jr = 6 to 1 Step -1
  If ows.Cells(jr, 7).Value = "Phase" then ows.Rows(jr).Delete
  Next jr
Another way is to back up jr every time you do a delete:
Code:
For jr = 1 to 6
  If ows.Cells(jr, 7).Value = "Phase" Then
    ows.Rows(jr).Delete
    jr = jr - 1
    End If
  Next jr
That works because the For...Next construction doesn't keep track of what jr was last time; it just adds 1 to jr and then checks, before going into the loop, to be sure it hasn't passed 6.

And the fact that I had to do the loop backward is why I had to have two loops in the first place. Until I noticed the above problem, I had a nice simple bit of logic with one loop, doing everything in one pass. Oh, well.
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate Cells with info contained in other cells Can I pre-populate cells with a formula? Box Excel 1 05-03-2014 11:55 PM
How to populate cells in Sheet2 with Data Source query using cell data from Sheet1 bobznkazoo Excel 2 03-27-2014 11:14 AM
Populate Cells with info contained in other cells How-TO format cells (FILL) by comparing cells zanat0s Excel 1 07-03-2012 04:27 AM
Count range cells eliminating merge cells danbenedek Excel 0 06-15-2010 12:40 AM
Populate cells in Word from a database hotlilshan Word 3 12-09-2008 01:51 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:25 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft