怎样找出表2与表1相同规格的金额，并自动录入到表1里面啊？

LL2011

'没跑过,原理应该没错咯,可能语句有错,你自己改哈
Sub change_click()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim myrng1 As Range
    Dim myrng2 As Range
    Dim myint1 As Integer
    Dim myint2 As Integer
    Dim myint3 As Integer
    Dim mystr1 As String
    Dim mystr2 As String
    Dim mystr3 As String
    Dim one1, one2, i1, i2, i3
    ThisWorkbook.Worksheets(1).Activate
    myint1 = ActiveSheet.UsedRange.Rows.Count
    Set myrng1 = ActiveSheet.Range("c7:c" & myint1 + 7 - 1)
    '在表1中建立辅助列p
    i1 = 7
    For Each one1 In myrng1
        ActiveSheet.Range("p" & i1).Value = CStr(one1.Value) & "*" & CStr(one1.Offset(0, 1).Value)
        i = i + 1
    Next
    '将SHEET2中有的商品代码填入表1中,将表2里的期末结存单价填入表1相对应的本期出库单价里
    With ThisWorkbook.Worksheets(2)
         myint2 = .UsedRange.Count
         Set myrng2 = .Range("b4:b" & myint2 + 4 - 1)
    End With
    i1 = i - 1
    For i2 = 7 To i1
        For Each one2 In myrng2
            mystr1 = Trim(one2.Value)
            myint3 = Len(mystr1)
            mystr2 = Mid(mystr1, 10, myint3 - 9)
            On Error Resume Next
            If mystr2 = ThisWorkbook.Worksheets(1).Range("k" & i2).Value Then
               one2.Offset(0, -1).Copy Destination:=ThisWorkbook.Worksheets(1).Range("a" & i2)
               one2.Offset(0, 10).Copy Destination:=ThisWorkbook.Worksheets(1).Range("j" & i2)
            End If
        Next
    Next i2
    '将表2里的原材料-进料-材料A300*500变成材料A300*50
    For Each one2 In myrng2
        mystr3 = Replace(1, Trim(one2.Value), "原材料-进料-材料", "材料", 1)
        one2.Value = mystr3
    Next
    '将表1中那些在表2里没有科目代码的商品所在行背景顏色修改為紅色
    ThisWorkbook.Worksheets(1).Range("a7:" & myint1 + 7 - 1).SpecialCells(xlCellTypeBlanks).Select
    i3 = Selection.Row
    ThisWorkbook.Worksheets(1).Rows(i3).Interior.Color = 255
    '刪除表1中輔助列p
    ThisWorkbook.Worksheets(1).columes(16).Delete
    '释放变量
    Set myrng1 = Nothing
    Set myrng2 = Nothing
    mystr1 = ""
    mystr2 = ""
    mystr3 = ""
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

buhuozhinian411

楼上好厉害呀！先谢谢!
我琢磨一下。

我连简单常用的函数运用还不熟悉，如何能理解如此深奥的语句？！

xiaofx11

建议用数据透视表，可以发到那里

buhuozhinian411

谢谢提醒。