这里有一个我试图实现的Excel示例。
食品集团/食品/苹果/香蕉/胡萝卜/Broccoli
我正在写一份If Then声明,试图将Foods与他们的团体相匹配。
(我知道Access会更容易,但我不想一直从Excel复制和粘贴到Access上。它只有6个不同的项目)
我制作的If Then编码只适用于第一个单元格。但我的循环不起作用。请帮助
Sub InsertFoodGroup()
Dim food As String, group As String
Dim i As Integer
i = Cells(2, 2).End(xlDown).Row
food = Range(Cells(2, 2), Cells(i, 2)).Value
Do While Cells(i, 2).Value <> ""
If food = "Apple" Then
group = "Fruit"
ElseIf food = "Banana" Then
group = "Fruit"
ElseIf food = "Carrot" Then
group = "Vegetable"
ElseIf food = "Broccoli" Then
group = "Vegetable"
Else
total = "false"
End If
i = i + 1
Loop
Range(Cells(2, 21), Cells(i, 21)).Value = group
End Sub
我可以建议使用一个非常简单的UDF:吗
Public Function foodToGroup(ByVal food As String) As Variant
Select Case food
Case "Apple", "Banana":
foodToGroup = "Fruit"
Case "Carrot", "Broccoli":
foodToGroup = "Vegetable"
Case Else
foodToGroup = False
End Select
End Function
因此,假设您的数据位于"A1:A100"中,您只需要在单元格"B1"中键入=foodToGroup(A1)
(或需要键入的任何位置),然后向下拖动函数,直到数据结束。
请注意,这可以通过一个简单的Excel公式轻松实现:=IF(OR(A1="Banana",A1="Apple"),"Fruit",IF(OR(A1="Carrot",A1="Broccoli"),"Vegetable",FALSE))