VBA Macro - Scan worksheet for specific value [Closed]

Report
Posts
1
Registration date
Monday September 3, 2018
Status
Member
Last seen
September 3, 2018
-
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
-
Hello,

Looking to create a VBA macro on Excel which scans a worksheet on the document for a specific value(s) and then tally up the results and exports it to a new worksheet.

So for example; if I had a worksheet consisting of hundreds of colours in a specific column, I'd need the macro to scan that column for all the cells containing the word "blue" and then generate a new worksheet showing "Blue: 15", to represent that blue appeared on the worksheet 15 times. Would also need to scan for other colours too so I'd be looking for multiple values.

Not too sure how to write out a macro like this using VB so any help would be massively appreciated.

1 reply

Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
Hi Eagletondl,

The code below will count the color blue and red (so you can see how to add more colors) in column A of the active sheet. Then place the result in "Sheet2", colors in column A and color count in column B.

Here is the code:
Sub RunMe()
Dim cBlue, cRed As Integer

For Each cell In Range("A:A")
    If cell.Value = "blue" Then
        cBlue = cBlue + 1
    ElseIf cell.Value = "red" Then
        cRed = cRed + 1
    End If
Next cell

With Sheets("Sheet2").Range("A" & Rows.Count)
    .End(xlUp).Offset(1, 0).Value = "Blue"
    .End(xlUp).Offset(0, 1).Value = cBlue
    .End(xlUp).Offset(1, 0).Value = "Red"
    .End(xlUp).Offset(0, 1).Value = cRed
End With

End Sub


I've split the color name and color count into 2 cells as it is not advised to put multiple values in a single cell.

Best regards,
Trowa