创建excel宏从表单中获取信息并将其附加到列表的底部 [英] Creating excel macro to take info from form and append it onto bottom of list

问题描述

我正在制作一个电子表格，以帮助跟踪我部门进行的每周会议的人员。我正在尝试通过使用宏从列表/表单复制值的自动化跟踪过程，我的部门的成员将输入参加者的电子邮件和日期。然后将电子邮件和日期添加在一起（= a& b）以生成值，该值将用于标记该人是否在该特定会议中存在。查看表单/表格的图片

会议后生成一份报告，以告知哪些人已经参加了会议，以及他们在接受电话的时间。在我收到这份报告之前，将其粘贴到原始列表的底部，但是由于列和表的长度发生变化，这种情况已经变得无效。我想要做的是从计算选项卡中获取电子表格的电子邮件，日期和价值，并将这些值附加到报告选项卡上的列表底部，而不会更改以前的任何信息。查看报告选项卡的图像

将值附加到报表底部后，我有另一个标签称为会议日期。这包含一个公式，通过用Y或N标记来确定个人是否存在。忘了说每周都有17个人参加这些会议。最终我想要这样做，以便如果在会议日期选项卡上没有出现在计算标签上输入的日期，则将日期添加到会议日期选项卡。

我还是非常新的Excel VB和宏，但是确实有一些编程经验。只是不在excel。如果有人可以帮助我，那将会很棒！

解决方案

这个答案是试图让你开始的。

如果您在互联网上搜索Excel VBA教程，您将获得许多点击。尝试一些，因为它们都是不同的，并选择你最喜欢的一个。通过该教程来获取Excel的一般感觉。我不相信你会成功找到一些相关代码，没有一般的感觉。

不要试图描述你的整个问题，因为我怀疑任何人都会回应。相反，尝试将您的问题分解为几个步骤，并寻求帮助。

例如，您需要确定会后报告中的行数因此您可以访问该数据。然后，您要将该数据添加到上一个列表的底部。在这两种情况下，您需要确定工作表中最后使用的行。 Excel VBA：如何找到工作表的最后一行？是一个简单的问题，您将能够找到多个答案。我在下面回答了这个问题。

我假设会后报告和您正在创建的列表位于不同的工作簿中。您的宏可能与列表相同的工作簿，也可能位于不同的工作簿中。宏可以访问自己的工作簿，任何其他正在开放的工作簿，或者可以根据需要打开许多其他工作簿。再次Excel VBA：如何处理几本工作簿？应该会导致大量的点击。

我没有尝试任何一个问题。我发现Excel VBA：有帮助，但您可能需要多次尝试才能找到正确的问题才能得到您的答案。但是，如果您的问题小巧精确，您应该总是能够找到答案。

让我们回到第一个问题。 Excel VBA的一个令人烦恼的特征是它们几乎总是达到类似效果的几种方式。创建一个新的工作簿，创建一个模块并将代码复制到它。运行宏 FindFinal（）。

此宏演示了几种查找最后一行和列的方法。每个方法都有其问题，我试图显示每个方法如何失败。在这个宏中有很多工作表访问，我相信会回报学习。它应该可以帮助您确定哪种方法适合您的每个需求。

  Option Explicit 
 Sub FindFinal（）
 
 Dim Col As Long 
 Dim Rng As Range 
 Dim Row As Long 
 
'尝试空工作表上的各种技术
调试。打印*****空工作表
 Debug.Print
 
带工作表（Sheet1）
 
 .Cells.EntireRow.Delete 
 
设置Rng = .UsedRange 
如果Rng不是，那么
 Debug.PrintUsed range is Nothing
 Else 
 Debug.PrintTop row of used范围是：& Rng.Row 
 Debug.Print使用范围的左列行是：& Rng.Column 
 Debug.Print使用范围中的行数为& Rng.Rows.Count 
 Debug.Print使用范围中的列数为：& Rng.Columns.Count 
 Debug.Print!!!请注意，工作表为空，但用户范围不是。 
 End If 
 
 Debug.Print
 
设置Rng = .Cells.Find（*，.Range（A1），xlFormulas， xlByRows，xlPrevious）
如果Rng没有，那么
 Debug.Print根据查找工作表是空的
 Else 
 Debug.Print根据查找最后一行包含价值是：& Rng.Row 
 End If 
 
 Debug.Print
设置Rng = .Cells.SpecialCells（xlCellTypeLastCell）
如果Rng不是，然后
调试。打印根据SpecialCells工作表是空的
 Else 
 Debug.Print根据SpecialCells，最后一行是：& Rng.Row 
 Debug.Print根据SpecialCells，最后一列是：&如果
 
 Debug.Print
 Row = .Cells（1，1）.End（xlDown）.Row 
 Debug.Print从A1下降到：A&行
 Row = .Cells（Rows.Count，1）.End（xlUp）.Row 
 Debug.Print从A& Rows.Count& 去：A& Row 
 Col = .Cells（1，1）.End（xlToRight）.Column 
 Debug.Print从A1开始到：& ColNumToCode（Col）& 1
 Col = .Cells（1，Columns.Count）.End（xlToLeft）.Column 
 Debug.PrintLeft from& Columns.Count& _ 
1转到：& ColNumToCode（Col）& 1
 
'将一些值和格式设置为工作表
 
 .Range（A1）。Value =A1
 .Range（A2 ）.Value =A2
 For Row = 5 To 7 
 .Cells（Row，A）。Value =A&行
下一个
对于行= 12到15 
 .Cells（Row，1）.Value =A&行
下一个
 
 .Range（B1）。值=B1
 .Range（C2）。Value =C2
。 （B16）Value =B6
 .Range（C17）。Value =C17
 
 .Columns（F）。ColumnWidth = 5 
 .Cells（18，4）.Interior.Color = RGB（128，128，255）
 .Rows（19）.RowHeight = 5 
 
 Debug.Print
 Debug.Print*****非空工作表
 Debug.Print
 
设置Rng = .UsedRange 
如果Rng不是，然后
 Debug.PrintUsed range is Nothing
 Else 
 Debug.Print使用范围的顶行是：& Rng.Row 
 Debug.Print使用范围的左列行是：& Rng.Column 
 Debug.Print使用范围中的行数为& Rng.Rows.Count 
 Debug.Print使用范围中的列数为：& Rng.Columns.Count 
 Debug.Print!!!请注意，第19行为空，但其高度已更改为used。 
 Debug.Print!!!请注意，第5列为空，但其宽度已更改不是used。 
 Debug.Print!!!请注意，列4为空，但包含彩色单元格为已用。 
 End If 
 
 Debug.Print
 
设置Rng = .Cells.Find（*，.Range（A1），xlFormulas， xlByRows，xlPrevious）
如果Rng没有，那么
 Debug.Print根据查找工作表是空的
 Else 
 Debug.Print根据查找最后一行包含公式是：& Rng.Row 
 End If 
'***注意：按列搜索不要按行搜索*** 
设置Rng = .Cells.Find（*，.Range（A1 ），xlFormulas，xlByColumns，xlPrevious）
如果Rng不是，然后
 Debug.Print根据查找工作表是空的
 Else 
 Debug.Print根据找到包含公式的最后一列是：& Rng.Column 
 End If 
'***注意：查找返回一个单元格，搜索的性质
'会影响它找到的内容。比较下面的SpecialCells。 
 
 Debug.Print
设置Rng = .Cells.SpecialCells（xlCellTypeLastCell）
如果Rng不是，然后
 Debug.Print根据SpecialCells工作表是空
 Else 
 Debug.Print根据SpecialCells，最后一行是：& Rng.Row 
 Debug.Print根据SpecialCells，最后一列是：& R $。
如果
 
 Debug.Print
 Row = 1 
 Do While True 
 Debug.PrintDown from A&行& 去： 
 Row = .Cells（Row，1）.End（xlDown）.Row 
 Debug.PrintA&行
如果Row = Rows.Count然后退出执行
循环
 
结束与
 
与工作表（Sheet2）
 
 .Cells.EntireRow.Delete 
 
 .Range（B2）。值=B2
 .Range（C3）。值=C3
 .Range（B7）。Value =B7
 .Range（B7：B8）。合并
 .Range（F3）Value =F3
。范围（F3：G3）。合并
 
 Debug.Print
 Debug.Print*****尝试合并单元格
 
设置Rng = .UsedRange 
如果Rng没有，那么
 Debug.PrintUsed range is Nothing
 Else 
 Debug.Printused range is：&替换（Rng.Address，$，）
 End If 
 
 Debug.Print
设置Rng = .Cells.Find（*，.Range （A1），xlFormulas，xlByRows，xlPrevious）
如果Rng不是，那么
 Debug.Print根据查找工作表是空的
 Else 
 Debug.Print 根据查找最后一个单元格是：&替换（Rng.Address，$，）
如果
设置Rng = .Cells.Find（*，.Range（A1），xlFormulas，xlByColumns，xlPrevious） 
如果Rng没有，然后
 Debug.Print根据查找工作表是空的
 Else 
 Debug.Print根据查找最后一个单元格列是：& ;替换（Rng.Address，$，）
 End If 
 Debug.Print!!!请注意，Find可以看到B7而不是F3。 
 
 Debug.Print
设置Rng = .Cells.SpecialCells（xlCellTypeLastCell）
如果Rng不是，然后
 Debug.Print根据SpecialCells工作表是空
 Else 
 Debug.Print根据SpecialCells，最后一行是：& Rng.Row 
 Debug.Print根据SpecialCells，最后一列是：& R $。
结束如果
 
结束
 
 End Sub 
函数ColNumToCode（ByVal ColNum As Long）As String 
 
 Dim Code As String 
 Dim PartNum As Long 
 
'最后更新于3 Feb 12。适用于处理三个字符代码。 
如果ColNum = 0然后
 ColNumToCode =0
 Else 
代码=
尽管ColNum> 0 
 PartNum =（ColNum  -  1）Mod 26 
代码= Chr（65 + PartNum）&代码
 ColNum =（ColNum  -  PartNum  -  1）\ 26 
循环
结束如果
 
结束函数
  / pre> 
 
 
在上面的代码中，我使用诸如 .Range（B2）之类的语句直接访问工作表单元格值=B2 。当您将数据从一个工作表移动到另一个工作表时，这可能很慢。另一种方法是使用数组。
  Dim Rng As Range 
 Dim ShtValues as Variant 
 
与工作表（Xxxx）
设置Rng = .Range（.Cells（Row1，Col1），.Cells（Row2，Col2））
结束
 
 ShtValues = Rng.Value 
  
变体是一个可以容纳任何数组的变量。  ShtValues = Rng.Value 将 ShtValues 转换为二维数组，保存Rng中的所有值。处理数组中的值在工作表中访问的速度要快得多。
 
 
 
  .Range（.Cells（Row1，Col1），.Cells Row2，Col2））可能是创建一个范围的最简单的方法，它将单元格（Row1，Col1）指定为左上单元格，单元格（Row2，Col2）作为右下角。 / p> 
 
 
如果我理解正确，您要将数据从会议后报告移动到列表中，但报表和列表中的列的顺序是不同的。这表明您需要将数据作为列移动。使用 .Range（.Cells（Row1，Col1），.Cells（Row2，Col2）），使用Col1 = Col2，可以定义一个列的范围。 / p> 
 
 
  Rng1.Copy目的地：= Cell2 
  
上述语句将将Rng1的内容复制到从Cell2开始的范围。报告中的每一列数据可能是复制数据的最简单方法。
 
 
 
希望以上内容能让您有一个开始。 
I am currently working on a spreadsheet to help track individuals who attend a weekly meeting conducted by my department. I am trying to automate the process of tracking by using a macro to copy values from a list/form that a member of my department will enter the attendees email and the date. The email and date will then be added together (=a&b) to generate a value and that value will be used to mark whether the individual is present or not at that particular meeting. View Image of form/table 



A report is generated after the meeting to tell which individuals have attended and how long they were on the call for. Before I was taking this report and pasting it onto the bottom of the original list but this has become inefficient as the columns and table length have changed.  What I would like to do is take the emails, dates, and value on spreadsheet from the calculate tab and have those values append onto the bottom of the list on the reports tab without altering any of the previous information. View Image of reports tab 



After the values have been appended to the bottom of the report, I have another tab called meeting dates. This contains a formula that will determine whether the individual was present or not by marking it with either "Y" or "N". Forgot to mention that every week it is the same 17 individuals that are attending these meetings.  Eventually I would like to have it so that if the date entered on the calculate tab is not present on the meeting dates tab, add the date to the meeting dates tab. 

I am still very new to Excel VB and macros however do have some programming experience. Just not in excel. If somebody could help me, that would be awesome!
解决方案
This answer is an attempt to get your started.

If you search the internet for "Excel VBA Tutorial" you will get many hits.  Try a few because they are all different and pick the one you like best.  Work through that tutorial to get a general feel for Excel.  I do not believe you will be successful finding bits of relevant code without that general feel.

Do not try to describe your entire problem because I doubt anyone will respond.  Instead try to break your problem down into little steps and seek help with those steps.

For example, you will need to determine the number of rows in the post-meeting report so you can access that data.  You then want to add that data to the bottom of the previous list.  In both cases you need to determine the last used row in a worksheet.  "Excel VBA: How to find last row of worksheet?" is a simple question and you will be able to find multiple answers.  I give my response to that question below.

I assume the post-meeting report and the list you are creating are in different workbooks.  Your macro could be in the same workbook as the list or it could be in a different workbook.  Macros can access their own workbooks, any other workbook that happens to be open or they can open as many other workbooks as required.  Again "Excel VBA: How do I work with several workbooks?" should result in plenty of hits.

I have not tried either of my questions.  I find "Excel VBA:" helps but you may require several attempts before you find the just the right question to get the answer you seek.  But if your question is small and precise you should always be able to find an answer.

Let's return to the first question.  An irritating feature of Excel VBA is that they are almost always several ways of achieving a similar effect.  Create a new workbook, create a module and copy the code below to it.  Run the macro FindFinal().

This macro demonstrates several methods of finding the last row and column.  Every method has its problems and I have tried to show how how each method can fail.  There is a lot of worksheet access within this macro which I believe will repay study.  It should help you decide which method is appropriate for each of your requirements.
Option Explicit
Sub FindFinal()

  Dim Col As Long
  Dim Rng As Range
  Dim Row As Long

  ' Try the various techniques on an empty worksheet
  Debug.Print "***** Empty worksheet"
  Debug.Print ""

  With Worksheets("Sheet1")

    .Cells.EntireRow.Delete

    Set Rng = .UsedRange
    If Rng Is Nothing Then
      Debug.Print "Used range is Nothing"
    Else
      Debug.Print "Top row of used range is: " & Rng.Row
      Debug.Print "Left column row of used range is: " & Rng.Column
      Debug.Print "Number of rows in used range is: " & Rng.Rows.Count
      Debug.Print "Number of columns in used range is: " & Rng.Columns.Count
      Debug.Print "!!! Notice that the worksheet is empty but the user range is not."
    End If

    Debug.Print ""

    Set Rng = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious)
    If Rng Is Nothing Then
      Debug.Print "According to Find the worksheet is empty"
    Else
      Debug.Print "According to Find the last row containing a value is: " & Rng.Row
    End If

    Debug.Print ""
    Set Rng = .Cells.SpecialCells(xlCellTypeLastCell)
    If Rng Is Nothing Then
      Debug.Print "According to SpecialCells the worksheet is empty"
    Else
      Debug.Print "According to SpecialCells the last row is: " & Rng.Row
      Debug.Print "According to SpecialCells the last column is: " & Rng.Column
    End If

    Debug.Print ""
    Row = .Cells(1, 1).End(xlDown).Row
    Debug.Print "Down from A1 goes to: A" & Row
    Row = .Cells(Rows.Count, 1).End(xlUp).Row
    Debug.Print "up from A" & Rows.Count & " goes to: A" & Row
    Col = .Cells(1, 1).End(xlToRight).Column
    Debug.Print "Right from A1 goes to: " & ColNumToCode(Col) & "1"
    Col = .Cells(1, Columns.Count).End(xlToLeft).Column
    Debug.Print "Left from " & Columns.Count & _
                "1 goes to: " & ColNumToCode(Col) & "1"

    ' Add some values and formatting to worksheet

    .Range("A1").Value = "A1"
    .Range("A2").Value = "A2"
    For Row = 5 To 7
      .Cells(Row, "A").Value = "A" & Row
    Next
    For Row = 12 To 15
      .Cells(Row, 1).Value = "A" & Row
    Next

    .Range("B1").Value = "B1"
    .Range("C2").Value = "C2"
    .Range("B16").Value = "B6"
    .Range("C17").Value = "C17"

    .Columns("F").ColumnWidth = 5
    .Cells(18, 4).Interior.Color = RGB(128, 128, 255)
    .Rows(19).RowHeight = 5

    Debug.Print ""
    Debug.Print "***** Non-empty worksheet"
    Debug.Print ""

    Set Rng = .UsedRange
    If Rng Is Nothing Then
      Debug.Print "Used range is Nothing"
    Else
      Debug.Print "Top row of used range is: " & Rng.Row
      Debug.Print "Left column row of used range is: " & Rng.Column
      Debug.Print "Number of rows in used range is: " & Rng.Rows.Count
      Debug.Print "Number of columns in used range is: " & Rng.Columns.Count
      Debug.Print "!!! Notice that row 19 which is empty but has had its height changed is ""used""."
      Debug.Print "!!! Notice that column 5 which is empty but has had its width changed is not ""used""."
      Debug.Print "!!! Notice that column 4 which is empty but contains a coloured cell is ""used""."
    End If

    Debug.Print ""

    Set Rng = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious)
    If Rng Is Nothing Then
      Debug.Print "According to Find the worksheet is empty"
    Else
      Debug.Print "According to Find the last row containing a formula is: " & Rng.Row
    End If
    ' *** Note: search by columns not search by rows ***
    Set Rng = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious)
    If Rng Is Nothing Then
      Debug.Print "According to Find the worksheet is empty"
    Else
      Debug.Print "According to Find the last column containing a formula is: " & Rng.Column
    End If
    ' *** Note: Find returns a single cell and the nature of the search
    '           affects what it find.  Compare SpecialCells below.

    Debug.Print ""
    Set Rng = .Cells.SpecialCells(xlCellTypeLastCell)
    If Rng Is Nothing Then
      Debug.Print "According to SpecialCells the worksheet is empty"
    Else
      Debug.Print "According to SpecialCells the last row is: " & Rng.Row
      Debug.Print "According to SpecialCells the last column is: " & Rng.Column
    End If

    Debug.Print ""
    Row = 1
    Do While True
      Debug.Print "Down from A" & Row & " goes to: ";
      Row = .Cells(Row, 1).End(xlDown).Row
      Debug.Print "A" & Row
      If Row = Rows.Count Then Exit Do
    Loop

  End With

  With Worksheets("Sheet2")

    .Cells.EntireRow.Delete

  .Range("B2").Value = "B2"
  .Range("C3").Value = "C3"
  .Range("B7").Value = "B7"
  .Range("B7:B8").Merge
  .Range("F3").Value = "F3"
  .Range("F3:G3").Merge

    Debug.Print ""
    Debug.Print "***** Try with merged cells"

    Set Rng = .UsedRange
    If Rng Is Nothing Then
      Debug.Print "Used range is Nothing"
    Else
      Debug.Print "Used range is: " & Replace(Rng.Address, "$", "")
    End If

    Debug.Print ""
    Set Rng = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious)
    If Rng Is Nothing Then
      Debug.Print "According to Find the worksheet is empty"
    Else
      Debug.Print "According to Find the last cell by row is: " & Replace(Rng.Address, "$", "")
    End If
    Set Rng = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious)
    If Rng Is Nothing Then
      Debug.Print "According to Find the worksheet is empty"
    Else
      Debug.Print "According to Find the last cell by column is: " & Replace(Rng.Address, "$", "")
    End If
      Debug.Print "!!! Notice that Find can ""see"" B7 but not F3."

    Debug.Print ""
    Set Rng = .Cells.SpecialCells(xlCellTypeLastCell)
    If Rng Is Nothing Then
      Debug.Print "According to SpecialCells the worksheet is empty"
    Else
      Debug.Print "According to SpecialCells the last row is: " & Rng.Row
      Debug.Print "According to SpecialCells the last column is: " & Rng.Column
    End If

  End With

End Sub
Function ColNumToCode(ByVal ColNum As Long) As String

  Dim Code As String
  Dim PartNum As Long

  ' Last updated 3 Feb 12.  Adapted to handle three character codes.
  If ColNum = 0 Then
    ColNumToCode = "0"
  Else
    Code = ""
    Do While ColNum > 0
      PartNum = (ColNum - 1) Mod 26
      Code = Chr(65 + PartNum) & Code
      ColNum = (ColNum - PartNum - 1) \ 26
    Loop
  End If

End Function
In the code above, I access worksheet cells directly with statements such as .Range("B2").Value = "B2".  This can be slow particularly when you are moving data from one worksheet to another.  An alternative approach is to use arrays.
Dim Rng As Range
Dim ShtValues as Variant

With Worksheets("Xxxx")
  Set Rng = .Range(.Cells(Row1, Col1), .Cells(Row2, Col2))
End With 

ShtValues = Rng.Value
A Variant is a variable that can hold anything including an array.  ShtValues = Rng.Value converts ShtValues to a two-dimensional array hold all the values within Rng.  Processing values within an array is much faster that accessing them in the worksheet.

.Range(.Cells(Row1, Col1), .Cells(Row2, Col2)) is perhaps the easiest way of creating a range specifying the worksheet area with Cells(Row1, Col1) as the top left cell and Cells(Row2, Col2) as the bottom right.

If I understand correctly, you want to move data from the post-meeting report to the list but the sequence of columns in the report and list are not the same.  This suggests you need to move the data as columns.  Using .Range(.Cells(Row1, Col1), .Cells(Row2, Col2)) and with Col1 = Col2, you can define a range that is a column.
Rng1.Copy Destination := Cell2
The above statement will copy the contents of Rng1 to the range starting at Cell2.  A statement like this for each column of data in the report may be the easiest way of copying the data.

I hope the above gives you a start.

                        
这篇关于创建excel宏从表单中获取信息并将其附加到列表的底部的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！