首页 文章

宏以跨工作表匹配单元格，然后复制并粘贴相关单元格

提问于
浏览 次
0

我正在尝试创建一个宏，它将Sheet的A列（“Company”）中的值与“Current”的E列中的值相匹配 . 如果匹配，并且右边的单元格28（“当前”）为空，则我想将单元格复制到“公司”中相应单元格的右侧并粘贴它 . 它应循环遍历Sheet A列（“Company”）的所有值 . 为了增加难度，我很乐意如果我可以实现一种ActiveSheet实用程序，那么我可以将它应用到其他工作表而不仅仅是“公司” . 这是我的...

Option Explicit

Sub CopyPaster()

InvestorName As String

InvestorName = ActiveCell.Value

With Sheets("Current")

For i = 11 To 500:
    If i = InvestorName And Cells(i, 27) = 0 Then
        Sheets("Company").ActiveCell.Offset(0, 3).Copy          
        Sheets("Current").Cells(i, 28).PasteSpecial                
Next i

End Sub

首先显示当前工作表，第二个图像是我要复制的公司工作表之一的示例 .

vba excel-vba excel

1 回答

  • 1

    根据给出的信息，我认为这就是你所追求的 . 请仔细检查我是否选择了正确的列，如果没有要查看的数据，很难猜到你所指的是什么 .

    Sub StackExchangeVlookup()

Dim Company As Worksheet
Dim Current As Worksheet

'set worksheets with the names of the sheets to compare
Set Company = Sheets("Company")
Set Current = Sheets("Current")

Dim myRange As range
Dim myCell As range
'Set the range to the column you want to replace empty cells in. I think I counted
'correctly, but maybe not. If AF is not correct, will also need to change the 32
Set myRange = range("AF1", Cells(Rows.count, 32).End(xlUp))

'if the cell is empty in the column AF (or whichever is the one you want, then use
'the VLOOKUP function.
For Each myCell In myRange
    If Len(myCell) = 0 Then
    'VLOOKUP will get the cell in column E of the row of the blank space in AF, compare
    'it to Column A in Company, and then return the value to the right of the cell found
    'in the Company sheet.
        myCell.Value = _
    "=VLOOKUP(" & Current.Name & "!E" & myCell.Row & "," & Company.Name & "!A:B, 2, FALSE)"
    End If
Next myCell

End Sub
    回复于

相关问题