☰

Excel 宏帮助：从 R 2 开始，读取 Col B、WS 1，在 Col E、WS 2 中找到相同的内容，然后将 Col I、WS 1 中的单元格复制到 Col G、WS 2 的单元格中

tag-icon • tag-icon microsoft-excel vba macros
Excel 宏帮助：从 R 2 开始，读取 Col B、WS 1，在 Col E、WS 2 中找到相同的内容，然后将 Col I、WS 1 中的单元格复制到 Col G、WS 2 的单元格中

需要在 Excel 中创建一个宏来执行以下操作：

从第 2 行开始，读取工作表 1 的 B 列（需要在数据前加上 PW- 前缀，并修剪字符串末尾的空格），在工作表 2 的 E 列中找到相同的值，匹配的行将工作表 1 的 I 列单元格内容复制到工作表 2 的 G 列单元格中，移至工作表 1 的第 3 行、B 列，依此类推

工作表 1

在尝试搜索工作表 2 之前，需要将工作表 1 的 B 列中的数据 = “5555BGR32” 转换为“PW-555BGR32”（在前缀处添加 PW- 并删除空格）

工作表 2

希望这能说明 Russell

工作表 1 = SohGB.csv 工作表 2 = Stockimport.xlsm

宏代码：

Sub Macro1()  
'Import sohGB sheet  
    ChDir "/"  
    Workbooks.Open Filename:="C:\work\sites\Solve-NT\Gorilla Workwear\Suppliers\Portwest Stock feed\sohGB.csv"  
    Sheets("sohGB").Select  
    Sheets("sohGB").Copy Before:=Workbooks("Stockimport.xlsm").Sheets(1)  
    Windows("sohGB.csv").Activate  
    ActiveWindow.Close  
' Find last row  
    Sheets("sohGB").Select  
      With ActiveSheet  
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row  
    End With  
' LOOP  
'pick up data from sohGB  
        For x = 2 To LastRow  
        Sheets("sohGB").Select  
        Item = Cells(x, 2)  
        Item = Trim(Item)  
        Item = "PW-" & Item  
        StockLevel = Cells(2, 9)  
'Find Item Stockimport  
            Sheets("Stockimport").Select  
              Columns("E:E").Select  

           FoundCell = Selection.Find(What:=Item, After:=ActiveCell, LookIn:= _
                xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows,  SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False).Activate  
            If FoundCell Is Nothing Then  
                 GoTo NotFound  
            End If  
           'Overwrite stock level  
            ActiveCell.Offset(0, 2) = StockLevel  
NotFound:  
    Next x  
End Sub

运行此宏时出现以下错误 错误代码 调试

答案1

这是承诺的重写。我无法完全测试代码，但它确实编译无误。

编辑：我做了一项额外的改进，进一步简化了代码。

Option Explicit

Sub Macro1()

    'Declare your variables!
    Dim x As Long, lastRow As Long, stockLevel As Long
    Dim cRef As String, foundAt As Variant

    With Workbooks("StockImport.xlsm")

        'you can directly insert a CSV as a worksheet if you treat it as a template
        'note: must be Sheets.Add, not Worksheets.Add
        .Sheets.Add Before:=.Sheets(1), _
                    Type:="C:\work\sites\Solve-NT\Gorilla Workwear\Suppliers\Portwest Stock feed\sohGB.csv"

        ' Find last row
        With .Worksheets("sohGB")
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With

        ' LOOP
        For x = 2 To lastRow

            'record child reference and stock level
            With .Worksheets("sohGB")
                'Trim only removes leading/trailing spaces; it doesn't remove interim spaces
                cRef = "PW-" & Replace(.Cells(x, "B").Value2, Chr(32), vbNullString)
                stockLevel = .Cells(x, "I").Value2
            End With

            'Find Item in Stockimport
            With .Worksheets("StockImport")

                'alternate method of location
                foundAt = Application.Match(cRef, .Range("E:E"), 0)

                If Not IsError(foundAt) Then

                    'set new stock level
                    .Cells(foundAt, "G") = stockLevel

                End If

            End With

        Next x

    End With

    'I wasn't sure what this was supposed to do so I put it here. It's not needed above.
    ChDir "/"

End Sub

相关内容