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
– JNevill, Commented Aug 2, 2018 at 14:22

Batteredburrito · Accepted Answer · 2018-08-02 15:16:53Z

1

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

edited Aug 2, 2018 at 15:16

Batteredburrito

5991 gold badge7 silver badges38 bronze badges

answered Aug 2, 2018 at 14:21

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

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

2 Comments

Batteredburrito Over a year ago

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 :)

Comintern Over a year ago

@Batteredburrito Nice catch on my rows\columns mistake and thanks for the edit.

Collectives™ on Stack Overflow

Excel VBA: Loop InputBox and add values to cell range

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related