Excel VBA: Loop InputBox and add values to cell range

Question

I have the following code:

Private Sub ChangeAccountNames_Click()

Dim AccountName As String
Dim Rng As Range
Dim InputQuestion As String
Dim i As Integer
Dim s As Worksheet

Set s = Sheets("Essential Info")
i = Cells(3, 9)

Do

Set Rng = Sheets("Essential Info").Range("I2:I13")
InputQuestion = "Please enter in a bank account name: " & vbNewLine & "Click Close once you are done"
AccountName = InputBox(InputQuestion)
Rng.Value = AccountName
i = i + 1

Loop Until i = 10

End Sub

What I need it to do in the following order is:

Open Inputbox when button is clicked - Done
Ask the user to enter in an account name - Done
Store the account name in a string - Done (AccountName Variable)
Write the AccountName to cell I3 in the sheet "Essential Info"- Kinda works. It inserts to the right sheet but writes it to all cells I3:I13, believe its the Do loop being laid out wrong
Shift cell I3 down so it now becomes I4 ready for the next name input- Not Functional, need help. Was thinking that I may be able to add 1 cell to a Cells(0,0) variable?
Only allow the cell to go up to I13 - Working?? Stores this value in i and does count up each loop, it doesn't overshoot.
Loop back to step 1 until i hits the limit defined OR the user closes the InputBox - It loops but i believe the loop where all my issues lie

There are currently unused variables and really messy bits of info in here that I've added as personal placeholders. Stepthrough seems to behave almost how I need it too but I know this is completely messy

I think each one of these questions should be a separate question here on SO. IT's a little broad to answer in one shot. You declare `rng` as `Range("I2:I13")` but you want to write to `I3`. Why is `I2` part of that range? Why is `I4:I13` part of that range? What good is having all of those cells hanging out in this one range variable good for? If you are stuck on using that `rng` as is then you can reference the exact cell you want to write to `Rng.Cells(2).Value = AccountName` However, why not just `Sheets("Essential Info").Range("I3").Value = AccountInfo` Isn't that a lot more clear? — JNevill, Aug 02 '18 at 14:22

score 1 · Accepted Answer · edited Aug 02 '18 at 15:16

If I understand correctly what you're looking to do, you should only be setting the Rng to the starting cell instead of the complete range. The quick fix is to set the initial position outside the loop and keep it in sync with i by offsetting the column.

To allow for an early exit if you don't get a value from the user in the InputBox, just check the return value for a vbNullString.

I'm guessing you need something more like this (untested):

Set Rng = Sheets("Essential Info").Range("I2")
Do
    InputQuestion = "Please enter in a bank account name: " & vbNewLine & "Click Close once you are done"
    AccountName = InputBox(InputQuestion)
    If AccountName <> vbNullString Then
        Rng.Value = AccountName
        i = i + 1
        'Offset by Row down
        Set Rng = Rng.Offset(1, 0)
        'Offset by column right 
        'Set Rng = Rng.Offset(0, 1)
    End If
Loop Until i = 10 Or AccountName = vbNullString

This does the job. I edited the Rng.Offset to offset by rows instead of the specified columns. Thank you so much for your help :) — Batteredburrito, Aug 02 '18 at 15:15
@Batteredburrito Nice catch on my rows\columns mistake and thanks for the edit. — Comintern, Aug 02 '18 at 15:17

Excel VBA: Loop InputBox and add values to cell range

1 Answers1

Linked