0

Excel如何把資料依序往下放?

2.第3個搜尋到的是豬肉,因為第2個儲存格已經是豬肉,會忽略或覆蓋第2個
3.雞肉放在"肉類(A欄)"的第4個?
4.往下以此類推

Dim i
For i = 2 To 9
If Cells(i, 3) = '肉類' Then
Cells(2, 1) = Cells(i, 3)
End If
Next

rogeryao iT邦大師 1 級 ‧ 2021-01-29 11:19:29 檢舉
"各項食品皆已定義好哪個是肉類哪個是蔬菜", 定義在哪個欄位 ?

5 個回答

2

iT邦大神 1 級 ‧ 2021-01-29 12:30:48

As-Is

To-Be

darban iT邦新手 5 級 ‧ 2021-01-29 12:54:03 檢舉

www

0
eric_hsu58
iT邦新手 5 級 ‧ 2021-01-29 13:56:53

i 控制搜尋肉類或是蔬菜
j 是第一 Column 肉類已經寫到第幾格
k 是第二 Column 蔬菜已經寫到第幾格

'--------------------------------------------
Dim i,j,k
j=2
k=2
For i = 2 To 9
If Cells(i, 4) = '肉類' Then
Cells(j, 1) = Cells(i, 3)
j = j + 1
Else
Cells(k, 1) = Cells(i, 3)
k = k + 1
End If
Next

1
rogeryao
iT邦大師 1 級 ‧ 2021-01-29 16:22:36

``````Private Sub CommandButton1_Click()
Dim x, y As Integer
x = 2
y = 2
RecordCount = Worksheets("工作表1").Range("C65535").End(xlUp).Row

For i = 2 To RecordCount
If Cells(i, 4) = "肉類" Then
Cells(x, 1) = Cells(i, 3)
x = x + 1
ElseIf Cells(i, 4) = "蔬菜類" Then
Cells(y, 2) = Cells(i, 3)
y = y + 1
End If
Next

'工作表1 A 欄位去除重複
'工作表1 B 欄位去除重複

'排序:工作表1 A 欄位
ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("工作表1").Sort
.SetRange Range("A2:A" & RecordCount)
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'排序:工作表1 B 欄位
ActiveWorkbook.Worksheets("工作表1").Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("工作表1").Sort
.SetRange Range("B2:B" & RecordCount)
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("A2:A" & RecordCount).Select
With Selection
.HorizontalAlignment = xlCenter '中間對齊
End With

Range("B2:B" & RecordCount).Select
With Selection
.HorizontalAlignment = xlCenter '中間對齊
End With

End Sub
``````

darban iT邦新手 5 級 ‧ 2021-01-29 18:37:33 檢舉

1
froce
iT邦大師 1 級 ‧ 2021-01-29 16:33:38

``````=IF(ISNA(MATCH(C2,\$A\$2:\$A\$4,0)),B1,A1)
``````
1

iT邦新手 3 級 ‧ 2021-01-29 16:37:47