VBA Macro - Scan worksheet for specific value Closed

Question

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.

TrowaD · Answer

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

VBA Macro - Scan worksheet for specific value

1 response

Similar discussions

Subscribe To Our Newsletter!