Do until i = the number of numbered cells in a specific sheet

Question

I'm trying to execute a macro several times. I want to execute it based on the number of cells containing a number within a specific column on a specific sheet.

I tried to write a do until loop, but I get a syntax error for the "do until i =" line.

Sub copy_paste_to_first_blank_row()

Dim i As Integer

i = 1

Do Until i = COUNT(input!E:E)

    Range("A1:D76").Select
    Selection.Copy
    Cells(Range("A1000000").End(xlUp).Row + 1, 1).Select
    ActiveSheet.Paste

i = i + 1

Loop

End Sub

Since in my given situation count(input!E:E) = 93, I would expect the loop to be run 92 times and then stop. I get the error message "compile error: syntax error" when I try to run it; it highlights the "do until" line.

...

Now I've tried

Dim i As Integer, x As Integer

Worksheets("input").Activate
x = Range("E:E").Count

i = 1

Do Until i = x

    Range("A1:D76").Select
    Selection.Copy
    Cells(Range("A1000000").End(xlUp).Row + 1, 1).Select
    ActiveSheet.Paste

i = i + 1

Loop

I get the error "Run-time error '6': Overflow".

There's a number of issues with this code, what is your goal? — Mark S., Aug 23 '19 at 19:51
`COUNT` is a worksheetfunction. Also you may want to find the last row and then use that for looping? To find last row, see [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) — Siddharth Rout, Aug 23 '19 at 19:52
My goal is to run a loop a given number of times. The number of times I want to run it is equal to count(input!E:E) — auditor, Aug 23 '19 at 20:00

score 0 · Answer 1 · answered Aug 23 '19 at 20:10

0

Integer is a 16-bit signed integer type, so the maximum value it can take is 32,767. Anything greater than that will raise an Overflow run-time error.

Fix it by declaring your row numbers as Long, a 32-bit signed integer type, with a maximum value well beyond the number of rows on an Excel worksheet.

answered Aug 23 '19 at 20:10

Mathieu Guindon

69,817
8
107
235

That got it to run, but now I have discovered a second problem. When VBA evaluates -- _x = Worksheets("input").range("E:E").Count_ -- it returns the total number of rows in excel, but I only have 93 cells in column E with numbers, so x should equal 93. – auditor Aug 23 '19 at 20:35
@auditor Sid has already given you a link for that. See [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba?noredirect=1&lq=1) – Mathieu Guindon Aug 23 '19 at 20:43
I see now. That ended up working by making _x = lastrow - 1_. If there's a way to return a value equal to the count of a given column, that would be ideal. I'm sure there is, but right now this is probably the best solution. – auditor Aug 23 '19 at 21:25
@auditor if you actually read the linked Q&A, you would find that `Range("E" & Rows.Count).End(xlUp).Row` gives you exactly that... – Mathieu Guindon Aug 23 '19 at 21:31

Do until i = the number of numbered cells in a specific sheet

1 Answers1