匹配名称并从工作表 1 复制到匹配名称旁边的工作表 2

发布于 2024-12-11 18:31:58 字数 1635 浏览 0 评论 0原文

我有一个 Excel 工作表，其中 A 列中包含工作表 1 的名称，B 列中包含金额

。我还有另一个工作表，即工作表 2，A 中的名称与工作表 1 中的名称相同，B 列为空白。

如何检查工作表 1 的名称以检查工作表 2 的名称，如果它们匹配，则将工作表 1 上该名称旁边的金额复制到工作表 2 上该名称旁边的匹配名称旁边的单元格中？ sheet1 上的名字每天都会变化。

我已经尝试过这个但一无所获。

Sub Macro1()
'
' Macro1 Macro
'
    Dim RowIndex As Integer 
    Sheets("Sheet1").Select
    RowIndex = Cells.Row
    While DoOne(RowIndex)
        RowIndex = RowIndex + 3
    Wend
End Sub


Function DoOne(RowIndex As Integer) As Boolean
    Dim Key
    Dim Target
    Dim Success
    Success = False
    If Not IsEmpty(Cells(RowIndex, 1).Value) Then
        Key = Cells(RowIndex, 1).Value

        Sheets("sheet2").Select

        Set Target = Columns(2).Find(Key, LookIn:=xlValues)

        If Not Target Is Nothing Then
            Rows(Target.Row).Select
            Selection.Copy
            Sheets("Sheet1").Select
            Rows(RowIndex + 1).Select
            Selection.Insert Shift:=xlDown
            Rows(RowIndex + 2).Select
            Application.CutCopyMode = False
            Success = True
        End If

    End If
    DoOne = Success
End Function

表 1：

A                                    B

A One Preservation            $16.00 

A&D Recovery, Inc.            $8,108.46 

A&S Field Services, Inc.      $4,941.56 

A&T Jax Inc                   $1,842.48

表 2：

A                                        B - blank cell

A One Preservation - Calvin & Renee 

A&D Recovery, Inc. - Drew & Adam    

A&S Field Services, Inc. - Aaron    

A&T Jax Inc - Tyson

原文

I have an Excel sheet with names in column A and an amount in column B for sheet 1.

I have a another sheet that is sheet2 with names in A just like in sheet 1 and column B is blank.

How can I check sheet 1 A name to check with sheet2 A name, if they match then take amount next to that name on sheet1 and copy the amount into the cell next to the matching name on sheet2 next to the name? The names on sheet1 change daily.

I have tried this and get nothing.

Sub Macro1()
'
' Macro1 Macro
'
    Dim RowIndex As Integer 
    Sheets("Sheet1").Select
    RowIndex = Cells.Row
    While DoOne(RowIndex)
        RowIndex = RowIndex + 3
    Wend
End Sub


Function DoOne(RowIndex As Integer) As Boolean
    Dim Key
    Dim Target
    Dim Success
    Success = False
    If Not IsEmpty(Cells(RowIndex, 1).Value) Then
        Key = Cells(RowIndex, 1).Value

        Sheets("sheet2").Select

        Set Target = Columns(2).Find(Key, LookIn:=xlValues)

        If Not Target Is Nothing Then
            Rows(Target.Row).Select
            Selection.Copy
            Sheets("Sheet1").Select
            Rows(RowIndex + 1).Select
            Selection.Insert Shift:=xlDown
            Rows(RowIndex + 2).Select
            Application.CutCopyMode = False
            Success = True
        End If

    End If
    DoOne = Success
End Function

Sheet 1:

A                                    B

A One Preservation            $16.00 

A&D Recovery, Inc.            $8,108.46 

A&S Field Services, Inc.      $4,941.56 

A&T Jax Inc                   $1,842.48

Sheet 2:

A                                        B - blank cell

A One Preservation - Calvin & Renee 

A&D Recovery, Inc. - Drew & Adam    

A&S Field Services, Inc. - Aaron    

A&T Jax Inc - Tyson

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

热情消退 2024-12-18 18:31:58

此代码使用索引/匹配解决方案从sheet2 复制sheet1 中匹配的B 值。该代码将使用变量工作表名称

空白单元格被忽略
第二个工作表上的不匹配被标记为“不匹配”。

代码通过仅更新值从第二张表的 B 列中删除公式

更新：如果您的第二个工作表名称与sheet1相同，但右侧有一个“-some text”，则使用代码的更新部分

 使用 rng1.Offset(0, 1)
    .FormulaR1C1 = "=IF(RC[-1]<>"""",IF(NOT(ISERROR(MATCH(LEFT(RC[-1],FIND("" -"",RC[-1]) )-1),'" & ws1.Name & "'!C[-1],0))),INDEX('" & ws1.Name & "'!C[-1],0))),INDEX('" & ws1.Name & "'!C,MATCH(LEFT(RC[-1],FIND("" -"",RC[-1])-1),'" & ws1.Name & "'!C[-1] ,0)),""不匹配""),"""")"
    .值 = .值
结束于

原版

 子 QuickUpdate()
    调暗 ws1 作为工作表
    Dim ws2 作为工作表
    调暗 rng1 作为范围
    设置 ws1 = 表(1)
    设置 ws2 = 表(2)
    设置 rng1 = ws2.Range(ws2.[a1], ws2.Cells(Rows.Count, "A").End(xlUp))
    使用 rng1.Offset(0, 1)
        .FormulaR1C1 = "=IF(RC[-1]<>"""",IF(NOT(ISNA(MATCH(RC[-1],'" & ws1.Name & "'!C[- 1],0))),INDEX('" & ws1.Name & "'!C,MATCH(RC[-1],'" & ws1.Name & "'!C,MATCH(RC[-1],'" & ws1.Name &) "'!C[-1],0)),""不匹配""),"""")"
        .值 = .值
    结束于
结束子

This code uses an Index/Match solution to copy the matched B values from sheet1 from sheet2. The code will work with variable sheet names

blank cells are ignored
Non-matches on the second sheet are flagged as "no match".

The code removes the formulae from column B on the second sheet by updating with values only

Update: if you second sheet names are the same as sheet1, but have a " -some text" to the right, then use this updated part of the code

 With rng1.Offset(0, 1)
    .FormulaR1C1 = "=IF(RC[-1]<>"""",IF(NOT(ISERROR(MATCH(LEFT(RC[-1],FIND("" -"",RC[-1])-1),'" & ws1.Name & "'!C[-1],0))),INDEX('" & ws1.Name & "'!C,MATCH(LEFT(RC[-1],FIND("" -"",RC[-1])-1),'" & ws1.Name & "'!C[-1],0)),""no match""),"""")"
    .Value = .Value
End With

original

 Sub QuickUpdate()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    Set rng1 = ws2.Range(ws2.[a1], ws2.Cells(Rows.Count, "A").End(xlUp))
    With rng1.Offset(0, 1)
        .FormulaR1C1 = "=IF(RC[-1]<>"""",IF(NOT(ISNA(MATCH(RC[-1],'" & ws1.Name & "'!C[-1],0))),INDEX('" & ws1.Name & "'!C,MATCH(RC[-1],'" & ws1.Name & "'!C[-1],0)),""no match""),"""")"
        .Value = .Value
    End With
End Sub

回复收藏 0 原文

单身狗的梦 2024-12-18 18:31:58

为什么不使用VLOOKUP函数呢？

Sheet1 在 A 列中包含您的姓名，在 B 列中包含您的值。
Sheet2 在 A 列中包含查找名称，在 B 列中输入：

=VLOOKUP(A1,Sheet1!$A$1:$B$n,2,FALSE)

其中“n”是 Sheet1 表中的行数。

唯一的问题是，如果在 Sheet1 中找不到该名称，它将显示 #N/A。可能有一种方法可以使用条件放入备用条目。

Why not use the VLOOKUP function?

Sheet1 has your names in column A, and values in column B.
Sheet2 has your lookup names in column A, and in column B, you put:

=VLOOKUP(A1,Sheet1!$A$1:$B$n,2,FALSE)

Where 'n' is the number of rows in your Sheet1 table.

The only issue with this is it will put an #N/A if it can't find the name in Sheet1. There's likely a way to put in an alternate entry using a conditional.

回复收藏 0 原文

~没有更多了~