vba 根据匹配将行信息从一张纸复制到另一张纸
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17819576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copying Row Info from one sheet to another based on match
提问by thindery
I have an excel book that has two sheets: 1) Import 2) Pricing Rules.
我有一本有两张纸的 Excel 书：1）导入 2）定价规则。
Pricing Rules Sheet
Pricing Rules Sheet
The Acolumn is what I need to match on. Example values include STA_PNP4, STA_PST.. and others. There are potentially around 50 different rows in the sheet, and it will continue to grow over time. Then for each row, there are pricing values in columns B to CF.
该A列是我需要匹配的内容。示例值包括STA_PNP4, STA_PST.. 等。工作表中可能有大约 50 行不同的行，并且会随着时间的推移继续增长。然后对于每一行，列中有定价值B to CF。
Import Sheet
Import Sheet
This sheet has the same number of columns, but only Column Ais filled out. Example values include STA_PNP4_001_00, STA_PNP4_007_00, STA_PST_010_00.. and many more.
此工作表具有相同的列数，但仅A填写了Column 。示例值包括STA_PNP4_001_00, STA_PNP4_007_00, STA_PST_010_00.. 等等。
What I need to do:
我需要做什么：
If the text in Import SheetColumn Abefore the second "_" matches the column identifer in Pricing Rules SheetColumn A, copy the rest of B to CFof Pricing Rulessheet for that row into the Import sheetfor the row it matched on.
如果Import SheetColumn A第二个“_”之前的文本与 中的列标识符匹配Pricing Rules SheetColumn A，则将该行的其余B to CF工作Pricing Rules表复制到Import sheet其匹配的行中。
Any idea on where to begin with this one?
知道从哪里开始吗？
回答by Kamal G
Why don't you do it using formulas only?
你为什么不只用公式来做呢？
Assuming :
假设 ：
1.) Data in Import Sheet is
1.) 导入表中的数据是
(col A)
（可乐）
STA_PNP4_007_00
STA_PNP4_007_00
STA_PNP4_001_00
STA_PNP4_001_00
STA_PNP4_001_00
STA_PNP4_001_00
. .
. .
2.) Data in Pricing Rules Sheet
2.) 定价规则表中的数据
(Col A) (col B) (ColC) (Col D) .......
(Col A) (col B) (ColC) (Col D) .......
STA_PNP4 1 2 3 .....
STA_PNP4 1 2 3 .....
STA_PST 4 5 6 .....
STA_PST 4 5 6 .....
STA_ASA2 7 8 9 .....
STA_ASA2 7 8 9 .....
Then write this formula in B1 cell of Import Sheet =IFERROR(VLOOKUP(LEFT(A1,FIND("",A1,FIND("",A1)+1)-1),PricingRules!$A$1:$CF$100,2,0),"")
然后在导入表的 B1 单元格中写下这个公式 =IFERROR(VLOOKUP(LEFT(A1,FIND(" ",A1,FIND("",A1)+1)-1),PricingRules!$A$1:$CF$100, 2,0),"")
Drag it down in column B
在 B 列中向下拖动
and For Column C , D just change index num from 2 to (3 for C) , (4 for D) and like that.
对于列 C ， D 只需将索引 num 从 2 更改为 (3 for C) ， (4 for D) 等等。
回答by pnuts
Because it will continue to grow over timeyou may be best using VBA. However, even with code I would start by applying the ‘groups' via formula, so as not to have a spreadsheet overburdened with formulae and hence potentially slow and easy to corrupt. Something like part of @xtremeExcel's solution which I repeat because the underscores have been treated as formatting commands in that answer:
因为它会随着时间的推移继续增长，所以最好使用 VBA。但是，即使使用代码，我也会首先通过公式应用“组”，以免电子表格因公式而负担过重，因此可能会缓慢且容易损坏。类似于@xtremeExcel 解决方案的一部分，我重复了一遍，因为在该答案中下划线已被视为格式化命令：
=LEFT(A1,FIND("_",A1,1+FIND("_",A1))-1)
I'd envisage this (copied down) as an additional column in your Import Sheet- to serve as a key field to link to your Pricing Rules Sheet. Say on the extreme left so available for use by VLOOKUP across the entire sheet.
我想将此（复制下来）作为您的附加列Import Sheet- 作为链接到您的Pricing Rules Sheet. 在最左边说，以便 VLOOKUP 在整个工作表中使用。
With that as a key field then either:
将其作为关键字段，然后：
- Write the code to populate
Pricing Rules Sheetas frequently as run/desired. Either populating ‘from scratch' each time (perhaps best for low volumes) or incrementally (likely advisable for high volumes). - Use VLOOKUP (as suggested). However with at least 84 columns and, presumably, many more than 50 rows that is a lot of formulae, though may be viable as a temporary ‘once off' solution (ie after population Copy/Paste Special/Values).
- A compromise. As 2. But preserve a row or a cell with the appropriate formulae/a and copy that to populate the other columns for your additions to your ColumnA and/or ColumnA:B.
- 编写代码以
Pricing Rules Sheet按运行/期望的频率填充。每次都“从头开始”填充（可能最适合小批量）或增量填充（可能建议大批量）。 - 使用 VLOOKUP（按照建议）。然而，至少有 84 列，并且大概有 50 多行，这是很多公式，尽管作为临时的“一次性”解决方案可能是可行的（即在填充复制/粘贴特殊值/值之后）。
- 妥协。作为 2. 但保留具有适当公式/a 的行或单元格并复制它以填充其他列，以便添加到 ColumnA 和/或 ColumnA:B。
回答by thindery
Thanks for the input guys.
感谢您的输入。
I got it implemented via a method like this:
我通过这样的方法实现了它：
{=VLOOKUP(LEFT($A4,7),PricingRules!A3:CF112,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84},FALSE)}
{=VLOOKUP(LEFT($A4,7),PricingRules!A3:CF112,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84},FALSE)}
That is my ugly function, applied across a whole row, to look up and copy from my pricing rules every column when it finds a match.
这是我的丑陋功能，应用于整行，在找到匹配项时从我的定价规则中查找和复制每一列。
回答by Naveen Goyal
Below is the function that I have created for above scenario. Its working as per the requirement that you have mentioned.
下面是我为上述场景创建的函数。它按照您提到的要求工作。
Sub CopyData()
Dim wb As Workbook
Dim importws As Worksheet
Dim PricingRulesws As Worksheet
Dim Pricingrowcount As Integer
Dim importRowCount As Integer
Dim FindValue As String
Dim textvalue As String
Dim columncount As Integer
Dim stringarray() As String
'Enter full address of your file ex: "C:\newfolder\datafile.xlsx"
Set wb = Workbooks.Open("C:\newfolder\datafile.xlsx")
'Enter the name of your "import" sheet
Set importws = Sheets("Import")
'Enter the name of your "Pricing" sheet
Set PricingRulesws = Sheets("PricingRules")
For Pricingrowcount = 1 To PricingRulesws.UsedRange.Rows.Count
FindValue = PricingRulesws.Cells(Pricingrowcount, 1)
For importRowCount = 1 To importws.UsedRange.Rows.Count
textvalue = importws.Cells(importRowCount, 1)
stringarray = Split(textvalue, "_")
textvalue = stringarray(0) & "_" & stringarray(1)
If FindValue = textvalue Then
For columncount = 2 To PricingRulesws.UsedRange.Columns.Count
importws.Cells(importRowCount, columncount) = PricingRulesws.Cells(Pricingrowcount, columncount)
Next columncount
End If
Next importRowCount
Next Pricingrowcount
End Sub
