Stop loop VBA (Application.InputBox)

Question

I'm doing a macro to calculate cell values from a given range (starting cell to ending cell) and iterate through the cells until it finds a null cell (no value in it).

So the workflow should be:

Starting cell (user defined);
Ending cell (user defined);
Loop through values until empty cell is found. If empty cell is found, next column (Note: I'm assuming that a empty cell is break of the column);
Loop through columns until end cell is reached;
If ending cell is reached, unless it's empty, loop through column until empty cell is found;
Stop the program.

It should also get the values from the active cell and calculate the sum when it changes column (for now it just an idea, not main problem).

The problem here that I'm having is that by allowing the user to choose it ending I'm stuck at stopping the loop when it reaches that ending cell.

This is what I've done so far (Note: No error handling on input yet, to be added later):

Sub test01()
    Dim startCell As Variant
    Dim endCell As Variant    

    Range("A1").Select ' Starting cell position
    startCell = Application.InputBox("A", "A", , , , , Type:=8) 'Start of range
    endCell = Application.InputBox("B", "B", , , , , Type:=8)   'End of range


    Do
        If IsEmpty(ActiveCell) Then
            ActiveCell.EntireColumn.Select
            ActiveCell.Offset(0, 1).Select

        Else
            'Calculate data
            ActiveCell.Offset(1, 0).Select

            If ActiveCell.Column And ActiveCell.Row = endCell Then
                Exit Do
            End If

        End If

    Loop

End Sub

Any sugestions on what I'm doing wrong and ways to improve it? Thanks :)

PS. New with vba.

Please explain that statement: If ActiveCell.Column And ActiveCell.Row = endCell Then. Thanks and regards, — Alexander Bell
– Alexander Bell, Commented Jun 8, 2015 at 1:56
I didn't knew how to get the absolute position of a cell, so I thought to reference it to it's column and adress :) Thanks for helping! — svacx
– svacx, Commented Jun 8, 2015 at 23:46

Alexander Bell · Accepted Answer · 2015-06-08 02:15:21Z

1

The business logic is a bit unclear, so based on best guess, the solution could be as shown in the following Excel VBA code snippet:

Sub test01()
    Dim startCell As Variant
    Dim endCell As Variant

    'Range("A1").Select ' Starting cell position
    startCell = Application.InputBox("A", "A", , , , , Type:=8).Address 'Start of range
    endCell = Application.InputBox("B", "B", , , , , Type:=8).Address   'End of range

    Range(startCell).Activate ' Starting cell position instead of "A1"

    Do
        If IsEmpty(ActiveCell) Then
            ActiveCell.EntireColumn.Select
            ActiveCell.Offset(0, 1).Select
            Exit Do
        Else
            'Calculate data
            ActiveCell.Offset(1, 0).Select
            If ActiveCell.Address = endCell Then
                Exit Do
            End If
        End If
    Loop
End Sub

Hope this may help.

answered Jun 8, 2015 at 2:15

Alexander Bell

7,9163 gold badges29 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

svacx Over a year ago

Throws a error. Run time error '1004' Method 'Range' of object '_Global' failed at the line Range(startCell).Activate. Why?

Alexander Bell Over a year ago

Because when InputBox pop-up you have to click on the appropriate cell, so it will appear in that box. Do it twice for the start and end rows: I have tested that part and it works fine. Best regards,

svacx Over a year ago

I've tested it, but it does not stop at the activecell adress

Alexander Bell Over a year ago

So, now you have another issue? Just click the appropriate cells and it will work. I am going to wrap this comments thread. The answer was given to you, and you can further modify the VBA code for your particular purpose. Please mark the answer accepted and should you have more questions, please post them separately. Thanks and regards,

Collectives™ on Stack Overflow

Stop loop VBA (Application.InputBox)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related