Populate Cells with info contained in other cells

EC37 · #1 07-15-2014, 09:17 AM

lol. super fast response time to the private message I just sent you to enlist your help...

I had similar thoughts on how to write it- but of course was using only what I know (such as Copy & Paste). I didn't realize you save the value and then set the value to a new column. My other thought was what you had shown me previously on how to Group specific rows based on "Phase" and "Sub-Phase". Such as:

Code:

 
ez = Range("M1").End(xlDown).Row 
  Dim eStart, eEnd
  eEnd = 1
  Do
    eStart = StartRow1(eEnd, "Phase", ez)
    If eStart = 0 Then Exit Do
    eEnd = EndRow1(eStart, ez)
    Range(eStart & ":" & eEnd).Group
    Loop

But I didn't know how to edit it for my needs.

Yes, details would be helpful.

But no, it is not obvious as to why you are doing the second look backward- hoping you could explain.

BobBridges · #2 07-16-2014, 04:44 AM

I'm not sure from what you wrote whether you know this already, but the most obvious way to save a value and use it later is to set a variable of your own, perhaps like this:

Code:

For jr = 2 To rz
  Select Case ows.Cells(jr, 7).Value
    Case "Phase"
    Case "Sub-Phase"
      col4 = ows.Cells(jr, 4).Value
    Case Else
      ows.Cells(jr, 3).Value = col4
    End Select
  Next jr

The name of the variable is pretty much up to you; there are certain values VBA won't let you use (you can look them up if you want) but mostly you can pick any name that makes sense to you.

"Group!" (he says thoughtfully); I didn't think of applying that here. (Pause to think about it a while.) But no, the problem with grouping is that what you really want is to copy the Sub-Phase value in D to the lower level in a new column; grouping won't help you with that. Interesting thought, though; I'll have to keep it in mind for other tasks where it might not ordinarily have occurred to me.

BobBridges · #3 07-16-2014, 05:00 AM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
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
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