Excel VBA:循环InputBox并将值添加到单元格范围 [英] Excel VBA: Loop InputBox and add values to cell range

问题描述

我有以下代码:

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. 单击按钮时打开输入框-完成
2. 要求用户输入帐户名-完成
3. 将帐户名存储为字符串-完成(AccountName变量)
4. 将AccountName写入工作表基本信息"中的单元格I3- Kinda可以使用.它插入到正确的工作表中，但是将其写入所有I3:I13单元格，认为其Do循环的布局错误
5. 向下移动单元格I3，以便现在可以将其变为I4，以便输入下一个名称-不起作用，需要帮助.想我可以将1个单元格添加到Cells(0,0)变量中吗?
6. 仅允许单元上升到I13-正在工作???将此值存储在i中，并且确实累加每个循环，不会超调.
7. 循环回到第1步，直到我达到定义的限制，或者用户关闭InputBox-它循环，但我相信所有问题都存在的循环

这里有一些未使用的变量和非常混乱的信息，我已将其添加为个人占位符.逐步操作似乎也可以满足我的需要，但我知道这完全是一团糟

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

推荐答案

如果我正确理解了您要做什么，则只应将 Rng 设置为起始单元格而不是完整范围.快速解决方案是将循环的初始位置设置为 ，并通过使列偏移来使其与 i 保持同步.

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.

如果在 InputBox 中没有从用户那里获得值，要允许提前退出，只需检查 vbNullString 的返回值.

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

这篇关于Excel VBA:循环InputBox并将值添加到单元格范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！