excel明細如下:
總表如下:
依年-月條件,提取不重覆值的進貨項目,寫進總表裡
小弟剛學習vba,只能試著google找了類似的程式,再試著加入年-月的條件讓他回傳,
但一直無法成功,程式碼如下,請教各位大大,條件年-月條件應該加到哪裡才適合呢?
Sub 取不重複值加條件()
Dim myList As New Collection, Cel As Range, itm, i As Integer
On Error Resume Next
For Each Cel In Sheets("資料明細").Range("b2:b999")
If Cel <> "" Then myList.Add Cel.Value, CStr(Cel.Value) '判斷單元格內容是否為空
Next
On Error GoTo 0
i = 3
For Each itm In myList
Sheets("總表").Cells(i, "a") = Format(itm, "@")
i = i + 1
Next
'資料回傳完成後做排序
Sheets("總表").Range("a3:a999").Select
Selection.Sort key1:=Range("a3"), order1:=1, Header:=x1yes
End Sub
可以試試看這樣,Sheets("001")是來源,Sheets("003")總表,自己調整一下
Sub Btn1_Click()
Dim myList As New Collection, cell As Range, itm
Dim i As Integer, idxCol As Integer, idxRow As Integer
Dim str As String, str2 As String
Sheets("001").Range("A2:B999").Sort key1:=Range("A2"), order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
idxCol = 0
idxRow = 0
str = ""
str2 = ""
For Each cell In Sheets("001").Range("A2:A999")
If cell <> "" And str <> cell.Value Then
idxCol = idxCol + 1
idxRow = 1
str = cell.Value
str2 = ""
Sheets("003").Cells(1, idxCol) = cell.Value
End If
If cell.Value <> "" And str2 <> cell.Offset(0, 1).Value Then
idxRow = idxRow + 1
str2 = cell.Offset(0, 1).Value
Sheets("003").Cells(idxRow, idxCol) = cell.Offset(0, 1).Value
End If
Next
End Sub
CK豬 wrote:
芒果都被拿去做冰了,進不到貨
芒果好像都供不應求?
台灣的芒果乾貴的嚇人,去泰國才能1公斤1公斤的吃
帶入條件可以這樣用:
Sub GetDistinctFruit(month As String)
Dim myList As New Collection, cell As Range, itm, i As Integer
Dim str As String
Sheets("001").Range("A2:B999").Sort key1:=Range("A2"), order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
str = ""
For Each cell In Sheets("001").Range("B2:B999")
If cell <> "" And str <> cell.Value And cell.Offset(0, -1).Value = month Then
myList.Add cell.Value, CStr(cell.Value)
str = cell.Value
End If
Next
i = 3
For Each itm In myList
Sheets("003").Cells(i, "D") = Format(itm, "@")
i = i + 1
Next
End Sub
再看要怎麼帶入條件和處理輸出
Sub Btn2_Click()
GetDistinctFruit "2020-02"
End Sub
Sub Btn3_Click()
Dim str As String
str = InputBox("輸入年月(格式yyyy-mm):")
If str <> "" Then
GetDistinctFruit str
End If
End Sub
nsps5606 wrote:
芒果好像都供不應求?(恕刪)
有找到適合的程式,但不知道可否插入條件,
符合年-月條件才做回傳,可請大大再幫我看一下嗎
Sub 沒有芒果()
Dim i As Range
Dim n As Long
n = 2
For Each i In Sheets("資料明細").Range("a1:a9999")
If Application.WorksheetFunction.CountIf(Sheets("資料明細").Range("$A$1:" & i.Address), i) = 1 Then
Sheets("總表").Cells(n, 1) = i
n = n + 1
End If
Next
End Sub
關閉廣告