我可以使用用户窗体中的哪些 VBA 编码来根据行和列内容查找单元格，然后更新该单元格？

What VBA coding in a Userform can I use to find a cell based upon row & column content, and then update that cell?

提问by brian-welch

I am a complete beginner to VBA, truely.

我是一个完整的 VBA 初学者，真的。

I am trying to create a user form that will update the number of tasks a person completes on a given date as listed on a spreadsheet. I envision the Userform having two bottons (which are hidden and appear as the conditions of the subroutine). I am working on a Mac, by the way, and I know that will have VBA coding implications for use on a PC och vice versa.

我正在尝试创建一个用户表单，该表单将更新一个人在电子表格中列出的给定日期完成的任务数量。我设想用户窗体有两个按钮（它们被隐藏并作为子程序的条件出现）。顺便说一下，我正在 Mac 上工作，我知道这会对在 PC 上使用 VBA 编码产生影响，反之亦然。

The example sheet is this:

示例表是这样的：

The example Userform (a) is this:

示例用户表单 (a) 是这样的：

For argument's sake, let say I want to update or input the number of tasks that Greg completed on the 7th of May (2013/05/07).

为了论证起见，假设我想更新或输入 Greg 在 5 月 7 日（2013/05/07）完成的任务数量。

I would like for the Userform to proceed something like this:

我希望用户表单进行如下操作：

Entering person and date:

输入人和日期：

Then, retrieving number of tasks for Greg on the 7th after button click:

然后，在单击按钮后在第 7 次检索 Greg 的任务数：

Now, I want to input that I know Greg completed 6 tasks on the 7th and I click the second button (now visible and the first button hidden):

现在，我想输入我知道 Greg 在 7 日完成了 6 个任务，然后单击第二个按钮（现在可见，第一个按钮隐藏）：

And the result in the spreadsheet:

以及电子表格中的结果：

I ought to input some code here, but my skills and the completeness of the code are wanting. But I will put in what I have:

我应该在这里输入一些代码，但我的技能和代码的完整性不足。但我会放入我拥有的：

Option Explicit

'Subroutine when clicking the first ("find") button
Private Sub btnfind_Click()
    lbltasks.Vissible = True
    txttasks.Visible = True
    btnupdate.Visible = True
    btnfind.Visible = False

'Defining variables
    Dim pr01 As String
    Dim dt01 As Date
    Dim tsk01 As Integer

'Assigning variables to inputs
    pr01 = txtperson.Text
    dt01 = txtdate.Text
    tsk01 = txttask.Text

'Looking for Name in column "A"
    ' ? ? ?

'Looking for inut Date in row "1"
    ' ? ? ?

'Retrieving the existing number of tasks according to name and date
'and showing number in the 'tasks' text input box in the user form
     ' ? ? ?
End Sub

'Subroutine when clicking the Second ("update") button
Private Sub btnupdate_Click()

'Paste updated Number of tasks in appropriate cells according to name and date
'The new number of tasks should over write what was there previously
    ' ? ? ?

End Sub

Thanks in advance for any and all help!

在此先感谢您的任何帮助！

采纳答案by glh

This should work. Please study it and use the macro record function in Excel to grasp a lot more:

这应该有效。请研究它并使用Excel中的宏记录功能来掌握更多：

Option Explicit
Public frmName As Variant 'store row of name
Public frmDate As Variant 'store column of date

'Subroutine when clicking the first ("find") button
Private Sub btnfind_Click()
'Defining variables
    Dim pr01 As String
    Dim dt01 As Date
    Dim tsk01 As Integer

'Assigning variables to inputs
    pr01 = UserForm1.TextBox1.Text
    dt01 = UserForm1.TextBox2.Text
    tsk01 = UserForm1.TextBox3.Text

'Looking for Name in column "A"
    With ThisWorkbook.Worksheets("Sheet4")
        frmName = .Columns("A").Find(pr01).Row
    End With


'Looking for inut Date in row "1"
    With ThisWorkbook.Worksheets("Sheet4")
        frmDate = .Rows(1).Find(CDate(dt01)).Column
    End With

    If frmName Is Nothing Or frmDate Is Nothing Then
        'not found
        Exit Sub
    End If

'Retrieving the existing number of tasks according to name and date
'and showing number in the 'tasks' text input box in the user form
    With ThisWorkbook.Worksheets("Sheet4")
        UserForm1.TextBox3.Text = .Cells(frmName, frmDate)
    End With

End Sub

'Subroutine when clicking the Second ("update") button
Private Sub btnupdate_Click()

'Paste updated Number of tasks in appropriate cells according to name and date
'The new number of tasks should over write what was there previously
    With ThisWorkbook.Worksheets("Sheet4")
        .Cells(frmName, frmDate) = UserForm1.TextBox3.Text
    End With
End Sub