VBA Loop through the amount of rows given in InputBox

Question

I would like to loop through the amount of rows that are entered in the Input box +1.

Sub LoopEnter()
    myNum = Application.InputBox("Enter number")
    For Each r In Range ("A2":"A" & myNum +1)
        r.Offset (0.1) = "N"& r
        Next r
    End Sub

However, ("A2":"A" & myNum +1) is not recognized as a range. What's the correct way to make the range, for example (A2:A41) if the number entered in the InputBox is 40?

Well spotted. I updated my answer to reflect your insight :0) — 3-14159265358979323846264
– 3-14159265358979323846264, Commented Jul 20, 2018 at 9:30
You could also use Range ("A2").resize(myNum) and avoid concatenating. — SJR
– SJR, Commented Jul 20, 2018 at 9:32

3-14159265358979323846264 · Accepted Answer · 2018-07-20 09:30:05Z

7

Try this ...

Sub LoopEnter()
    myNum = Application.InputBox("Enter number")
    For Each r In Range ("A2:A" & myNum +1)
        r.Offset (0,1) = "N" & r.Row
    Next r
End Sub

You had the : in the wrong place ... it should be inside the string, not between two strings!

Updated as suggested by @Santosh, as you'd also missed .Row out ... r is a Range object, r.Row is the row number property of that Range object.

edited Jul 20, 2018 at 9:30

answered Jul 20, 2018 at 9:22

3-14159265358979323846264

3,2703 gold badges18 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

VBA Loop through the amount of rows given in InputBox

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related