从另一个工作表中的单元格引用中查找列中的数据，然后将一些数据复制到另一个工作表 [英] Find data in a column from a cell reference in another worksheet then copy some data to another worksheet

问题描述

我在工作簿中有3个工作表，第一个是信息表，其中单元格S1具有我需要在名为类的第二个表中找到的培训师名称。一旦在课程表（H栏）中找到培训师名称，我需要将该培训师名称放在输出表（下一个空行，A列）中。那么我还需要从Classes（A列），Grad Date（P列）和X到AB列的数据中获取Class Name。我似乎无法获得如何正确代码，因为我已经运行的代码，但它没有输入到输出表中的数据。我只测试了2个字段。

  Sub GetClassData（）
 Dim cls As Worksheet 
 Dim Dim OUT OUT））））））））））））））））））））））））））））））
设置trName = ）.Range（S1）
 
 cls.Select 
'查找最后一行数据
 FinalRow = Cells（Rows.Count，1）.End（xlUp）。行
'循环遍历每行
对于x = 2至FinalRow 
'根据列决定是否复制H 
 thisvalue = Cells（x，8）.Value 
如果thisvalue = trName.Value然后
 irow = Cells（Rows.Count，1）.End（xlUp）.Row + 1 
与shOUT 
 .Cells（irow，1）.Value = trName.Value 
 .Cells（irow，2）.Value = trName.Offset（，-7）.Value 
 End with 
 End If 
 Next x 
 End Sub 
  

解决方案

尝试下面的代码代码注释）：

  Option Explicit 
 
 Sub GetClassData（）
 
 Dim cls As Worksheet 
 Dim shOUT As Worksheet 
 Dim trName As Range 
 Dim x As Long 
 Dim iRow As Long 
 Dim FinalRow As Long 
 Dim thisvalue As String 
 
设置cls = Worksheets（Classes）
设置shOUT =工作表（输出）
设置trName =工作表（Info）。范围（S1）
 
使用cls 
'查找最后一行在A列中的数据
 FinalRow = .Cells（.Rows.Count，A）。End（xlUp）.Row 
'循环遍历每行
对于x = 2 To FinalRow 
'根据列决定是否复制H 
 thisvalue = .Range（H& x）.Value 
如果这个值就像trName.Value然后'<  - 检查名称
 iRow = shOUT.Cells（shOUT.Rows.Count，A）。End（xlUp）.Row + 1 
 
 shOUT.Range（A& iRow）.Value = thisvalue'<  -  get Trainer Name 
 shOUT.Range（B& iRow）.Value = .Range（A& x）.Value'<  - 获取类的名称
'添加您需要复制的其余部分
 
 End If 
下一个x 
结束
 
 End Sub 
  

I have 3 worksheets in the workbook, 1st is the Info sheet where cell S1 has the Trainer Name that i need to find in the 2nd sheet named Classes. once the Trainer Name is found in the Classes sheet (Column H), i then need to put that Trainer name in the Output sheet(next blank row, column A). Then i also need to get Class Name from Classes (column A), Grad Date(Column P) and a few more data in columns X to AB. i can't seem to get how to code this correctly as the code i have runs but it does not enter the data into the Output sheet. i've only tested 2 fields so far.

Sub GetClassData()
Dim cls As Worksheet
Dim shOUT As Worksheet
Set cls = Worksheets("Classes")
Set shOUT = Worksheets("Output")
Dim trName As Range
Set trName = Worksheets("Info").Range("S1")

cls.Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
    ' Decide if to copy based on column H
    thisvalue = Cells(x, 8).Value
    If thisvalue = trName.Value Then
        irow = Cells(Rows.Count, 1).End(xlUp).Row + 1
        With shOUT
            .Cells(irow, 1).Value = trName.Value
            .Cells(irow, 2).Value = trName.Offset(, -7).Value
        End With
    End If
Next x
End Sub

解决方案

Try the code below (explanations are inside the Code comments):

Option Explicit

Sub GetClassData()

Dim cls As Worksheet
Dim shOUT As Worksheet
Dim trName As Range
Dim x As Long
Dim iRow As Long
Dim FinalRow As Long
Dim thisvalue As String

Set cls = Worksheets("Classes")
Set shOUT = Worksheets("Output")
Set trName = Worksheets("Info").Range("S1")

With cls
    ' Find the last row of data in Column "A"
    FinalRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ' Loop through each row
    For x = 2 To FinalRow
        ' Decide if to copy based on column H
        thisvalue = .Range("H" & x).Value
        If thisvalue Like trName.Value Then ' <-- check the names
            iRow = shOUT.Cells(shOUT.Rows.Count, "A").End(xlUp).Row + 1

            shOUT.Range("A" & iRow).Value = thisvalue '<-- get Trainer Name
            shOUT.Range("B" & iRow).Value = .Range("A" & x).Value '<-- get Class Name for Classes
            ' add the rest of the thing you need to copy here

        End If
    Next x
End With

End Sub

这篇关于从另一个工作表中的单元格引用中查找列中的数据，然后将一些数据复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！