VBA从另一个工作表的特定列中提取数据。
VBA从另一个工作表的特定列中提取数据。
提问于 2020-02-18 06:53:21
我有两张从系统中提取出来的床单。对于sheet1(数据)，包含多列和内部的所有数据。对于sheet2(Get)，我有2列，如下所示。对于sheet2(Get)上的引用列NO2 (ID)，我希望在sheet1(数据)中搜索这个值，然后提取特定的列值。例如，我尝试在线搜索代码，并找到这段提取所有列值的代码。但是我只想提取带有黄色高亮显示的列，然后将这个值提取到sheet2(Get)中。能帮我修改这段代码吗？
注意:对于sheet2(Get)，A列和B列中的所有数据都已经预先填充好了，所以我想将Worksheet_SelectionChange更改为一个普通的子数据，然后使用宏运行这个子程序。有可能吗？
Column C (Get sheet) should extract from Column B (Data sheet)
Column D (Get sheet) should extract from Column M (Data sheet)
Column E (Get sheet) should extract from Column J (Data sheet)
Column F (Get sheet) should extract from Column L (Data sheet)
Column G (Get sheet) should extract from Column C (Data sheet)
Column H (Get sheet) should extract from Column G (Data sheet)sheet2(Get)
Sheet1(数据)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim k As Integer, i As Long, Str As Range
'row number
For i = 3 To Sheets("GetData").Range("a65536").End(3).Row
Set Str = Sheets("Data").Range("a:a").Find(Cells(i, 1).Value, , xlValues, xlWhole)
If Not Str Is Nothing Then
'column number
For k = 1 To 14
If k > 1 Then Sheets("GetData").Cells(i, k).Value = Sheets("Data").Cells(Str.Row, k).Value
Next k
Else
For k = 2 To 14
Sheets("GetData").Cells(i, k).Value = "Null"
Next k
End If
Next i
End Sub回答 2
Stack Overflow用户
回答已采纳
发布于 2020-02-18 07:44:06
一旦有了行，就可以像这样复制：
Dim col As Long
'...
'...
col = 3
For Each e In Array(2, 3, 7, 10) 'columns to fetch
Sheets("GetData").Cells(i, col).Value = Str.EntireRow.Cells(e).Value
col = col + 1
Next
'...
'...Stack Overflow用户
发布于 2020-02-20 06:56:11
Public Sub fill_data()
Dim k As Integer, i As Long, Str As Range, col As Long, e As Variant
'row number
For i = 2 To Sheets("GetData").Range("B65536").End(3).Row
Set Str = Sheets("Data").Range("A:A").Find(Cells(i, 2).Value, , xlValues, xlWhole)
If Not Str Is Nothing Then 'if not empty
col = 3
For Each e In Array(13, 2, 10, 3, 5, 12, 9) 'columns to fetch
Sheets("GetData").Cells(i, col).Value = Str.EntireRow.Cells(e).Value
col = col + 1
Next
Else 'if empty
For col = 3 To 9
'Sheets("GetData").Cells(i, col).Interior.ColorIndex = 16 'change cell color OR
Sheets("GetData").Cells(i, col).Value = "NOTFOUND" 'change cell text
Next col
End If
Next i
End Sub感谢蒂姆·威廉姆斯的帮助。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/60275315
复制相关文章
相似问题
腾讯云开发者
