Excel VBA Loop That Not Stops Based On Activecell Value

Question

I have a loop that needs to stop when activecell value is "BBSE", but it passes the cell and continues the loop. someone can help me with that? I cut rows from table in one workbbok and paste it to another. before the list in column F I have many blank cells, and because of that I am usind xldown. Here is the relevant code:

'Illuminators Worksheet
OP_wb.Activate
Range("F2").End(xlDown).Select


Do Until ActiveCell.Value = "BBSE"
    OP_wb.Activate
    Worksheets("Optic Main").Activate
    Range("F2").End(xlDown).Select
    Selection.EntireRow.Cut
    Demand_WB.Activate
    Worksheets("Illuminators").Activate
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste


Loop

Here is where I want to stop the loop in the red circle:

this is why I am using END.xlDown

This will never match, as you are trying to match the content of a column, against that of a single cell. — Luuklag, Sep 07 '17 at 08:58
But your `ActiveCell` is in column A! (and on a different sheet? and in a different workbook??) (except for the first time through the loop) — YowE3K, Sep 07 '17 at 08:58
Basically, stop using `Activate` and `Select`. See https://stackoverflow.com/q/10714251/6535336. — YowE3K, Sep 07 '17 at 09:02
@Luuklag The first time through the loop, the single cell represented by something like `OP_wb.ActiveSheet.Range("F2").End(xlDown)` is selected (I'm guessing `OP_wb` is a workbook). After that the single cell represented by `Demand_WB.Worksheets("Illuminators").Range("A" & Rows.Count).End(xlUp).Offset(1)` is selected. So it is never a column that is selected. (I think. Maybe I should check.) No, you were right, an entire row is selected on the second time through. — YowE3K, Sep 07 '17 at 09:04
@YowE3K I thought that `Range("F2").End(xlDown).Select` is an entire column. I assumed the matching should occur here, because he obviously wants to stop cutting rows when he meets the next header row. So I think implementing a loop here would work best. — Luuklag, Sep 07 '17 at 09:11
@Luuklag Argh - I get confused by ActiveCell and Select and Selection!! It appears that the `ActiveSheet.Paste` makes the **`Selection`** the entire row on the destination sheet, but the **`ActiveCell`** remains at the cell selected by `Range("A" & Rows.Count).End(xlUp).Offset(1).Select` - so my very first comment was correct after all. — YowE3K, Sep 07 '17 at 09:15
@YowE3K So he is checking against this value, on a sheet that he doesn't want to contain the value he is checking against. Just don't use a `Do While` loop I guess. — Luuklag, Sep 07 '17 at 09:19
I am having difficulties understanding what the code is trying to do. Is it trying to copy the information from sheet "Optic Main" in `OP_wb` to sheet "Illuminators" in `Demand_WB` in reverse order (i.e. bottom row ends up at the top) until you reach the source row containing "BBSE" in column F? — YowE3K, Sep 07 '17 at 09:26
I have blanks in F before the cells I select, because of that I am using xldown. — Rafael Osipov, Sep 07 '17 at 09:35
So, you want to find the first non-blank cell in column F, and copy the entire rows from there until the row containing "BBSE". That sounds like a job for `Find` and then one single copy statement. — YowE3K, Sep 07 '17 at 09:36

score 1 · Accepted Answer · answered Sep 07 '17 at 09:50

If I understand what you are trying to achieve correctly, I believe the following will achieve it:

Dim startRow As Long
Dim endRow As Long
With OP_wb.Worksheets("Optic Main")
    startRow = .Range("F2").End(xlDown).Row
    endRow = .Columns("F").Find(What:="BBSE", LookIn:=xlValues, LookAt:=xlWhole).Row
    .Rows(startRow & ":" & endRow).Cut
End With
With Demand_WB.Worksheets("Illuminators")
    .Range("A" & .Rows.Count).End(xlUp).Offset(1).Insert Shift:=xlDown
End With

Sixthsense · Answer 2 · 2017-09-07T09:08:43.453

0

May be try like this...

'Mentioning Starting Row Here
x = 2

Do
    'x refers to Row and F refer to column name
    With Cells(x, "F")
        'Exiting Do Loop once it finds the matching value using If statement
        If .Value = "BBSE" Then Exit Do
        OP_wb.Activate
        Worksheets("Optic Main").Activate
        .EntireRow.Cut
        Demand_WB.Activate
        Worksheets("Illuminators").Activate
        Range("A" & Rows.Count).End(xlUp).Offset(1).Select
        ActiveSheet.Paste
    End With
    'Incrementing row number here to move on to next row
    x = x + 1
Loop

edited Sep 07 '17 at 09:08

answered Sep 07 '17 at 09:06

Sixthsense

1,927
2
16
38

End if is not needed in VBA since we have not used Else statement :) – Sixthsense Sep 07 '17 at 09:09
1

@Sixthsense Actually, the correct answer is "`End If` is not needed because it is a single-line `If`". A single-line `If` can contain an `Else`, e.g. `If a = b Then c = 5 Else c = 6`. – YowE3K Sep 07 '17 at 09:10
Good to know. I'll remove my comment. – Luuklag Sep 07 '17 at 09:11
@YowE3K, Have you tried it? Please try and comment. – Sixthsense Sep 07 '17 at 09:12
Don't know who made -1 to me :( – Sixthsense Sep 07 '17 at 09:18
@Sixthsense Sorry, tried what? My comment above was agreeing with your statement that the `End If` is not needed, but just not the reason for it (that "we have not used Else statement"). Basically, a single-line `If` does not have an `End If`, while a multi-line `If` (irrespective of whether that multi-line `If` includes an `Else`) **does** require an `End If`. – YowE3K Sep 07 '17 at 09:18
@ YowE3K , Sorry, I misunderstood your statement. – Sixthsense Sep 07 '17 at 09:19
@Sixthsense The -1 wasn't from me. (Although your code probably still won't work, because I think the wrong sheet is being referred to by `Cells(x, "F")` on the second time through the loop.) – YowE3K Sep 07 '17 at 09:21
Have you seen the incremental value x = x+1? – Sixthsense Sep 07 '17 at 09:23
At the end of the first loop, `Demand_WB.Worksheets("Illuminators")` will be the active sheet. So the second time through (when `x` is 3) it will be equivalent to `With Demand_WB.Worksheets("Illuminators").Cells(3, "F")`. You then are activating `OP_wb.Worksheets("Optic Main")` but doing a cut of `Demand_WB.Worksheets("Illuminators").Cells(3, "F").EntireRow`, which is the row you pasted to that sheet the last loop, and pasting it back where it was (or perhaps one row below it?). – YowE3K Sep 07 '17 at 09:31
There isn't going to be a good answer for this question until it is rewritten without all the `Activate` and `Select` statements. It needs to be written to assign the source and destination sheets as `Worksheet` objects, and then just use those objects to refer to what needs to be used. But I am heading to bed, so I will leave that up to you (or Luuklag) to sort out. – YowE3K Sep 07 '17 at 09:34
@YowE3K, Yes I agree with you. I just wanted to give alternative to Do Until statement since OP is not aware of activecell. That changes needs to be done at OP's end. I just wanted to show the method :) – Sixthsense Sep 07 '17 at 09:40

Excel VBA Loop That Not Stops Based On Activecell Value

2 Answers2