EXCEL VBA编写代码提取的数据？

答案:3 悬赏:0 手机版

解决时间 2021-03-19 06:43

提问者网友：
2021-03-18 10:56

EXCEL VBA编写代码提取的数据？

最佳答案

五星知识达人网友：春色三分
2021-03-18 11:25

答：
Sub today()
    Dim Cell As Range, Cell3 As Range, IDRng As Range
    Dim Color As New Collection, Size As New Collection
    Dim i As Long, j As Long
    Dim ID As Long

    Set Cell = Sheets("Sheet4").Range("B2")
    Set Cell3 = Sheets("Sheet3").Range("D2")
    With Sheets("Sheet2")
        Set IDRng = .Range("G2:G" & .Cells(Rows.Count, "G").End(xlUp).Row)
    End With
    On Error Resume Next
    Do Until IsEmpty(Cell)
        If Cell <> Cell.Offset(1, 0) Then
            ID = Cell.Value
            Color.Add Split(Cell.Offset(0, 1), "+")(0), CStr(Split(Cell.Offset(0, 1), "+")(0))
            Size.Add Split(Cell.Offset(0, 1), "+")(1), CStr(Split(Cell.Offset(0, 1), "+")(1))
            For i = 1 To Color.Count
                ''依次填入不重复颜色项---[生成D列值]
                Cell3 = Color.Item(i)
                ''根据颜色项和goodsid，到Sheet2中查找specid---[生成C列值]
                For j = 1 To IDRng.Count
                    If IDRng.Cells(j, 1) = ID And IDRng.Cells(j, 1).Offset(0, -4) = "颜色" Then
                        Cell3.Offset(0, -1) = IDRng.Cells(j, 1).Offset(0, -6)
                        Exit For
                    End If
                Next
                ''---[生成F列值]
                Cell3.Offset(0, 2) = 1
                ''---[生成G列值]
                Cell3.Offset(0, 3) = i - 1
                Set Cell3 = Cell3.Offset(1, 0)
            Next
            For i = 1 To Size.Count
                Cell3 = Size.Item(i)
                For j = 1 To IDRng.Count
                    If IDRng.Cells(j, 1) = ID And IDRng.Cells(j, 1).Offset(0, -4) = "尺码" Then
                        Cell3.Offset(0, -1) = IDRng.Cells(j, 1).Offset(0, -6)
                        Exit For
                    End If
                Next
                Cell3.Offset(0, 2) = 1
                Cell3.Offset(0, 3) = i - 1
                Set Cell3 = Cell3.Offset(1, 0)
            Next
            For i = Color.Count To 1 Step -1
                Color.Remove i
            Next i
            For i = Size.Count To 1 Step -1
                Size.Remove i
            Next i
        Else
            Color.Add Split(Cell.Offset(0, 1), "+")(0), CStr(Split(Cell.Offset(0, 1), "+")(0))
            Size.Add Split(Cell.Offset(0, 1), "+")(1), CStr(Split(Cell.Offset(0, 1), "+")(1))
        End If
        Set Cell = Cell.Offset(1, 0)
    Loop
End Sub

全部回答

1楼网友：七十二街
2021-03-18 12:24

VBA各种问题帮解决

2楼网友：往事埋风中
2021-03-18 11:51

1
2
3
4
5
6
7

Sub Macro1()
dim years as string
years ="2000"
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.cells.AutoFilter Field:=4, Criteria1:=years
End Sub
以上代码定于years 变量，可根据设置年份
然后选择第一行，生成高级筛选，筛选条件是第四列，筛选内容是变量years

我要举报

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息