CODEWITHSUNDEEP
vbaexcel
Batteredburrito
Batteredburrito

Reputation: 589

Excel VBA: Loop InputBox and add values to cell range

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:

  1. Open Inputbox when button is clicked - Done
  2. Ask the user to enter in an account name - Done
  3. Store the account name in a string - Done (AccountName Variable)
  4. 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
  5. 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?
  6. 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.
  7. 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

Upvotes: 0

Views: 2039

Answers (1)

Comintern
Comintern

Reputation: 22185

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

Upvotes: 1

Related Questions