首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >多项映射表

问多项映射表
EN

Stack Overflow用户
提问于 2021-06-24 09:07:05
回答 2查看 93关注 0票数 0

我有一个映射表,用于匹配两个单独的表(Sheet1和Sheet2)的头。但是,如果我有这样的东西(左边有3列,右边有2列):

  • 基本上,我希望POS1 2019 EMP1等于HR、DEPARTMAENT、Employee1等等。Sheet1,Sheet2,映射,我能怎么做呢?(预先多谢!) Public Sub test() Application.ScreenUpdating = False堆栈" Sheet1“、" Sheet2”、“Application.ScreenUpdating”= True End Sub Sub堆栈( ByVal Sheet1作为字符串、ByVal Sheet2作为字符串)、Dim rng为范围、trgtCell为范围、src为工作表、trgt为工作表、助手为工作表Dim rngSrc为范围,rngDest As Range Dim sht作为工作表集src =工作表(“Sheet1”) Set trgt =工作表(“Sheet2”) Set助手=工作表(“映射”),对相交(.Rows(3),.UsedRange).SpecialCells(xlCellTypeConstants) Dim lkup中的每个src进行src(.Rows(3),.UsedRange).SpecialCells(XlCellTypeConstants)Dim lkup作为变体,辅助器lkup= Application.VLookup(rng.Value,.Range("D13:E“& .Cells(.Rows.Count,"D").End(xlUp).Row),2,如果不是IsError(lkup),则设置trgtCell = trgt.Range("$B$2:$F$7").Find(lkup,LookIn:=xlValues,lookat:=xlWhole),如果没有trgtCell,则.Range(rng.Offset(1),.Cells(.Rows.Count,rng.Column).End(xlUp)).Copy与trgt .Range(xlUp,“$”($)(1)& 3).PasteSpecial以“如果下一个rng结束”结束 结束与结束子
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2021-06-24 15:38:26

我认为字典是最适合这类问题的数据结构。请注意,要在VBA中使用字典,需要设置对脚本运行时库的引用。

工具->引用-> Microsoft脚本运行时

下面是一些适用于您提供的示例的代码:

代码语言:javascript
复制
Public Sub test()
  Application.ScreenUpdating = False
  stack2 "Sheet1", "Sheet2", "Mapping"
  Application.ScreenUpdating = True
End Sub


Public Sub stack(ByVal Sheet1 As String, ByVal Sheet2 As String, ByVal Mapping As String)
Dim rng As Range, src As Worksheet, trgt As Worksheet, helper As Worksheet
Dim sht As Worksheet
Dim dctCol As Dictionary, dctHeader As Dictionary
Dim strKey1 As String, strKey2 As String
Dim strItem As String, col As Integer

Set src = Worksheets(Sheet1)
Set trgt = Worksheets(Sheet2)
Set helper = Worksheets(Mapping)
        
'build a dictionary to lookup column based on 3 rows of headers
Set dctCol = New Dictionary
arr1 = src.Range("A1:F7") 'arrays are way faster than ranges
For j = 2 To UBound(arr1, 2) 'loop over data from columns B-F
    strKey1 = Trim(arr1(1, j)) & "," & Trim(arr1(2, j)) & "," & Trim(arr1(3, j)) 'comma delimit string
    dctCol(strKey1) = j 'j is the column number
Next

'build a dictionary to translate 2 headers to 3 headers
Set dctHeader = New Dictionary
arrHelp = helper.Range("A2:E6")
For i = 1 To UBound(arrHelp)
    strKey2 = Trim(arrHelp(i, 4)) & "," & Trim(arrHelp(i, 5)) '2 header key
    strItem = Trim(arrHelp(i, 1)) & "," & Trim(arrHelp(i, 2)) & "," & Trim(arrHelp(i, 3))
    dctHeader(strKey2) = strItem
Next

'update sheet2 with numbers from sheet1
arr2 = trgt.Range("A1:F6")
For j = 2 To 5
    'work backwards to find the column
    strKey2 = Trim(arr2(1, 2)) & "," & Trim(arr2(2, j)) '2 headers
    strKey1 = dctHeader(strKey2)
    col = dctCol(strKey1)
    
    'update the data for arr2
    For i = 3 To 6
        arr2(i, j) = arr1(i + 1, col)
    Next
Next

'write it back to spreadsheet
trgt.Range("M10").Resize(UBound(arr2), UBound(arr2, 2)) = arr2
End Sub
票数 1
EN

Stack Overflow用户

发布于 2021-06-24 12:04:02

请尝试下一个改编的代码。

代码语言:javascript
复制
Public Sub test()
  Application.ScreenUpdating = False
   stack "Sheet1", "Sheet2", "Mapping"
  Application.ScreenUpdating = True
End Sub

Public Sub stack(ByVal Sheet1 As String, ByVal Sheet2 As String, ByVal Mapping As String)
Dim rng As Range, trgtCell As Range, src As Worksheet, trgt As Worksheet, helper As Worksheet
            
Dim rngSrc As Range, rngDest As Range
Dim sht As Worksheet
            
Set src = Worksheets(Sheet1)
Set trgt = Worksheets(Sheet2)
Set helper = Worksheets(Mapping)
        
With src
    For Each rng In Intersect(.Rows(3), .UsedRange).SpecialCells(xlCellTypeConstants)
        Dim lkup As Variant

        With helper
            lkup = Application.VLookup(rng.Value, .Range("C2:E" & .Cells(.Rows.Count, "C").End(xlUp).Row), 3, False)
        End With
        If Not IsError(lkup) Then
                    
            Set trgtCell = trgt.Range("$B$2:$F$7").Find(lkup, LookIn:=xlValues, lookat:=xlWhole)
             Debug.Print trgtCell.Address
            If Not trgtCell Is Nothing Then
                With trgt
                    .Range(trgtCell.Offset(1), .Cells(.Rows.Count, trgtCell.Column).End(xlUp)).Copy
                End With
                .Range(Split(trgtCell.Address, "$")(1) & 4).PasteSpecial
           End If
        End If
    Next rng
End With
End Sub

请也更正第二次呼叫的潜艇。你把"Shee2“和"Sheet1”混在一起.

请测试并发送一些反馈。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/68112786

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档