Do While ActiveCell <> Range

Question

I have this VBA excel macro code

Sub fillcells()
    Range("J14").Select
    Do While ActiveCell <> Range("J902")
        ActiveCell.Copy
        ActiveCell.Offset(6, 0).Select
        ActiveCell.PasteSpecial
    Loop
End Sub

At first it was working fine but now sometimes when I try to run the macro the loop suddenly stops at cell J242, other times is arising an error 'mismatch type' and sometimes the macro just select cell J14 without doing the loop

(see [This](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) thread) — BruceWayne, Jun 20 '16 at 16:57
There are many things wrong with this code. What exactly do you want to achieve? Move a whole column downwards by 6 cells? — trincot, Jun 20 '16 at 17:02
I'm trying to copy the formula in cell J14 and then go down 6 cells, copy and paste again every 6 cells until cell J902 — Arturo Esparza, Jun 20 '16 at 17:07

score 3 · Accepted Answer · edited May 23 '17 at 12:08

3

Not sure what you want to do, but (as noted in the comments to your OP), don't use .Select/.Activate. The following should do what (I think) you wanted:

Sub fillcells()
Dim i& ' Create a LONG variable to count cells

For i = 14 To 901 Step 6
    Cells(i, 10).Offset(6, 0).FormulaR1C1 = Cells(i, 10).FormulaR1C1
Loop
End Sub

This will loop from cell J14 to J901, copy/paste* to a cell 6 rows offset.

* Note I didn't actually copy/paste. Since your original code used PasteSpecial, I'm assuming you just want the values pasted. In this case, you can set the two ranges/cells equal.

edited May 23 '17 at 12:08

Community

1
1

answered Jun 20 '16 at 17:06

BruceWayne

22,923
15
65
110

This is code is pasting the formulas without filling the series, I need for example in cell J20 "=G17+H17" , cell J26 "=G23+H23" – Arturo Esparza Jun 20 '16 at 17:32
1

@ArturoEsparza - I've edited my OP. Also, please read through [How to Ask](http://stackoverflow.com/help/how-to-ask), as it would have saved everyone some time had you explained a little more in your OP what you're trying to do. – BruceWayne Jun 20 '16 at 17:44

score 2 · Answer 2 · answered Jun 20 '16 at 17:13

Just an addition to what @BruceWayne already said: whenever you have this typical phenomenon that something happens only "sometimes" it is often a case of using keywords such as Active or Current or Selection. These are not specific but change each time that you call the macro. Whatever you have selected is the starting point. You might even start clicking around and thus change Selection while the macro is running. In short, you should start coding explicitly and don't allow VBA / Excel to assume / make the decision for you.

Let's start with Range("J14").Select. This line of code asks VBA to make already two assumptions:

If you have several Excel files open. Which Excel file should it start with?
Within the file there might be several sheets. On which of these sheets should J14 be selected?

Explicit coding means that you (hopefully at all times) be very specific what you are referring to. So, instead of just stating Range("J14") you should use:

ThisWorkbook.Worksheets("SheetNameYouWantToReferTo").Range("J14")

But is pointed out in the other answer, this is not even necessary in this case. Rather loop the rows as shown and use:

ThisWorkbook.Worksheets("SheetNameYouWantToReferTo").Cells(i, 10).Offset(6, 0).Formula = ThisWorkbook.Worksheets("SheetNameYouWantToReferTo").Cells(i, 10).Offset(i, 10).Formula

Since this is a bit lengthy you can shorting it by using a With statement:

With ThisWorkbook.Worksheets("SheetNameYouWantToReferTo")
    .Cells(i, 10).Offset(6, 0).Formula = .Cells(i, 10).Formula
End With

Do While ActiveCell <> Range

2 Answers2