Reputation:
Let's say I have a list of elements in A column and I want to find the first occurence of each element and mark it "YES" in B column, like this:
Column A | Column B |
---|---|
Apple | Yes |
Cheese | Yes |
Bread | Yes |
Cheese | No |
Cheese | No |
Bread | No |
Pineapple | Yes |
How can I do this in VBA?
Upvotes: 2
Views: 487
Reputation: 54807
Excel Formula
=IF(COUNTIF(A$2:A2,A2)=1,"Yes","No")
Compact
Sub FlagFirstOccurrencesCompact()
Const SourceFirstCellAddress As String = "A2"
Const DestinationColumn As String = "B"
Const YesFlag As String = "Yes"
Const NoFlag As String = "No"
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
With ws.Range(SourceFirstCellAddress)
Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, .Column).End(xlUp).Row
If lRow < .Row Then Exit Sub
Dim Formula As String
Formula = "=IF(COUNTIF(" & .Address(, 0) & ":" & .Address(0, 0) & "," _
& .Address(0, 0) & ")=1,""" & YesFlag & """,""" & NoFlag & """)"
With .Resize(lRow - .Row + 1).EntireRow.Columns(DestinationColumn)
.Formula = Formula
.Value = .Value
End With
End With
End Sub
Argumented
Sub FlagFirstOccurrencesTEST()
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
FlagFirstOccurrences ws.Range("A2"), "B", "Yep", "Nope"
End Sub
Sub FlagFirstOccurrences( _
ByVal SourceFirstCell As Range, _
ByVal FlagColumn As String, _
Optional ByVal YesFlag As String = "Yes", _
Optional ByVal NoFlag As String = "No")
With SourceFirstCell.Cells(1)
Dim lRow As Long
lRow = .Worksheet.Cells(.Worksheet.Rows.Count, .Column).End(xlUp).Row
If lRow < .Row Then Exit Sub
Dim Formula As String
Formula = "=IF(COUNTIF(" & .Address(, 0) & ":" & .Address(0, 0) & "," _
& .Address(0, 0) & ")=1,""" & YesFlag & """,""" & NoFlag & """)"
With .Resize(lRow - .Row + 1).EntireRow.Columns(FlagColumn)
.Formula = Formula
.Value = .Value
End With
End With
End Sub
Upvotes: 2