如何在VBA中使用两个变量来选择一系列行 [英] How to select a range of rows using two variables in VBA

问题描述

我是VBA的全新，我正在尝试写一个宏，将同一目录中所有工作簿的特定数据从主工作簿中拉出。尝试使用变量选择一系列数据时，我被困住，以便我可以将数据复制并粘贴到主工作簿中。我一直在观看视频并通过论坛阅读，但是看起来似乎无法让这个宏工作。

我有一个Excel表格列出了列A中的员工，所有数据我想复制关于B，C，D，E和F列中的员工（后续行）。因此，例如，第1行包含单元格A1中的第一个员工，然后第2至5行包含列B至F中的数据。第6行包含单元格A6中的下一个员工名称，并且其数据位于第7行至第9（柱BF）。我想复制2-5行并将它们粘贴到主工作簿中，然后复制7-9并粘贴到master，8-14等中。

我第一次尝试将两个变量定义为整数。然后，我尝试在列A中找到第一个员工的名称，然后选择行，并将第一个变量设置为等于该行。然后找到第二个员工的名字，选择前面的行，并将变量2设置为等于该行。然后使用这两个变量选择范围。这是我的代码如下：

  Sub SelectConsultantData（）
 Dim Consultant1 As Integer，Consultant2 As Integer 
 Dim ConsultantRange As Range 
 
列（A：A）。选择
 Selection.Find（What：=Andrew，After：= ActiveCell，LookIn：= xlValues，_ 
 LookAt：= xlPart，SearchOrder：= xlByRows，SearchDirection：= xlNext，_ 
 MatchCase：= False，SearchFormat：= False）.Activate 
 Consultant1 = Rows（ActiveCell.Row）.Select 
 Consultant1 = Consultant1 + 1 
列（A：A）。选择
 Selection.Find（What：=Bob，After：= ActiveCell，LookIn：= xlValues，_ 
 LookAt：= xlPart，SearchOrder：= xlByRows，SearchDirection：= xlNext，_ 
 MatchCase：= False，SearchFormat：= False）.Activate 
 Consultant2 = Rows（ActiveCell.Row）。选择
 Consultant2 = Consultant2  -  1 
设置ConsultantRange =范围（顾问1，顾问2）。选择
 
 End Sub 
  

任何想法我是什么在做错误的，或任何人能想到更好的办法吗？另外请让我知道，如果我需要提供更多的上下文。

提前感谢任何帮助。

解决方案

您的代码可以重写如下。避免在您的代码中使用选择。检查此链接以了解原因。

  Sub SelectConsultantData（）
 Dim Consultant1 As Integer，Consultant2 As Integer 
 Dim ConsultantRange As Range 
 
 Dim rngFind As Range 
设置rngFind =列（A：A）。Find（What：=Andrew，After：= Range（A1），LookIn：= xlValues，LookAt：= xlPart，SearchOrder： = xlByRows，SearchDirection：= xlNext）
 
如果没有rngFind是没有
 Consultant1 = rngFind.Row + 1 
结束如果
 
设置rngFind =列（A：A）。Find（What：=Bob，After：= Range（A1），LookIn：= xlValues，LookAt：= xlPart，SearchOrder：= xlByRows，SearchDirection：= xlNext）
 
如果没有rngFind是没有，然后
 Consultant2 = rngFind.Row  -  1 
如果
 
如果Consultant1> 0和顾问2> 0然后
设置ConsultantRange =范围（单元格（顾问1,2），单元格（顾问2,6））
结束如果
 
 End Sub 
  

I'm brand new to VBA and am trying to write a macro to pull specific data from all workbooks in the same directory into a master workbook. I'm getting stuck when trying to select a range of data using variables so that I can copy and paste the data into the master workbook. I've been watching videos and reading through forums, but can't seem to get the macro to work.

I have an Excel sheet that lists employees in column A, with all the data I want to copy about the employees in columns B, C, D, E, and F (in subsequent rows). So for example, row 1 contains the first employee in cell A1, and then rows 2 through 5 contains the data in columns B through F. Row 6 contains the next employee's name in cell A6, and the data about them resides in rows 7 through 9 (columns B-F). I want to copy rows 2-5 and paste them into the master workbook, and then copy 7-9 and paste into master, 8-14, and so on and so forth.

My first attempt was to define two variables as integers. Then I tried to find the name of the first employee in column A and select the row after, and set the first variable equal to that row. Then find the name of the second employee, select the row before and set variable 2 equal to that row. Then select the range using those two variables. Here's what my code looks like:

Sub SelectConsultantData()
Dim Consultant1 As Integer, Consultant2 As Integer
Dim ConsultantRange As Range

    Columns("A:A").Select
    Selection.Find(What:="Andrew", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Consultant1 = Rows(ActiveCell.Row).Select
    Consultant1 = Consultant1 + 1
    Columns("A:A").Select
    Selection.Find(What:="Bob", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Consultant2 = Rows(ActiveCell.Row).Select
    Consultant2 = Consultant2 - 1
    Set ConsultantRange = Range(Consultant1, Consultant2).Select

End Sub

Any idea what I'm doing wrong, or can anyone think of a better approach? Also please let me know if I need to provide further context.

Thanks in advance for any help.

解决方案

Your code can be re-written as below. Avoid using Select in your code. Check this link to know why.

Sub SelectConsultantData()
    Dim Consultant1 As Integer, Consultant2 As Integer
    Dim ConsultantRange As Range

    Dim rngFind As Range
    Set rngFind = Columns("A:A").Find(What:="Andrew", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If Not rngFind Is Nothing Then
        Consultant1 = rngFind.Row + 1
    End If

    Set rngFind = Columns("A:A").Find(What:="Bob", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If Not rngFind Is Nothing Then
        Consultant2 = rngFind.Row - 1
    End If

    If Consultant1 > 0 And Consultant2 > 0 Then
        Set ConsultantRange = Range(Cells(Consultant1, 2), Cells(Consultant2, 6))
    End If

End Sub

这篇关于如何在VBA中使用两个变量来选择一系列行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！