Search a range in a sheet

A/L Formula:
=COUNTIF(C8:F68,"A/L")

B/H Formula:
=COUNTIF(C8:F68,"B/H")

In VBA:

Sub Macro()
Dim AL As Long, BH As Long, Rng As Range
Set Rng = Range("C8:F68")

    AL = WorksheetFunction.CountIf(Rng, "A/L")
    BH = WorksheetFunction.CountIf(Rng, "B/H")

MsgBox "A/L = " & AL & " and B/H = " & BH

    Range("A1").Value = AL
    Range("A2").Value = BH

Set Rng = Nothing
End Sub

Just to be clear, you want to search EVERY sheet except SUMMARY for those text values, then insert the TOTAL counts on the Summary in column A?

I think this will do what you're after. By verifying the Names of sheets instead of the position, it won't matter how you organize the workbook in the future.

Option Explicit

Sub Search_NotClear()

Dim AL As Long, CL As Long, LT As Long, BH As Long, SL As Long
Dim US As Long, ML As Long, UL As Long, a As Long
Dim ws As Worksheet, Rng As Range
Sheets("Summary").Activate
Set Rng = Range("C8:F68")

'Collect values
    For Each ws In Worksheets
        ws.Activate
        If ws.Name <> "Summary" Then
            AL = AL + WorksheetFunction.CountIf(Rng, "A/L")
            CL = CL + WorksheetFunction.CountIf(Rng, "C/L")
            LT = LT + WorksheetFunction.CountIf(Rng, "L/T")
            BH = BH + WorksheetFunction.CountIf(Rng, "B/H")
            SL = SL + WorksheetFunction.CountIf(Rng, "S/L")
            US = US + WorksheetFunction.CountIf(Rng, "U/S")
            ML = ML + WorksheetFunction.CountIf(Rng, "M/L")
            UL = UL + WorksheetFunction.CountIf(Rng, "U/L")
            a = a + WorksheetFunction.CountIf(Rng, "A")
        End If
    Next ws

'Insert values into Summary
Sheets("Summary").Activate
    Range("A1").Value = AL
    Range("A2").Value = CL
    Range("A3").Value = LT
    Range("A4").Value = BH
    Range("A5").Value = SL
    Range("A6").Value = US
    Range("A7").Value = ML
    Range("A8").Value = UL
    Range("A9").Value = a

Set Rng = Nothing
End Sub

Shouldn't be hard at all. The code I've given is ready to use, simply insert it into your command button. It doesn't matter where the button is, the code will go to the sheets and get the data needed.

When inserting my macro into your command button, only insert the lines from

Dim...
.
.
.
Set Rng = Nothing

This can made really simple if you just click GO ADVANCED and use the paperclip icon to upload your sheet. Sometimes people forget to indicate something MAJOR about their sheet that inhibits simple macros like this one.

Try this:

Option Explicit

Sub Search_NotClear()
    Const sRng  As String = "C8:F68"

    Dim wks     As Worksheet
    Dim v       As Variant
    Dim i       As Long
    Dim ai(1 To 9) As Long

    'Collect values
    For Each wks In Worksheets
        If wks.Name <> "Summary" Then
            i = 0
            For Each v In Array("A/L", "C/L", "L/T", "B/H", "S/L", "U/S", "M/L", "U/L", "a")
                i = i + 1
                ai(i) = ai(i) + WorksheetFunction.CountIf(wks.Range(sRng), v)
            Next v
        End If
    Next wks

    Worksheets("Summary").Range("A1:A9").Value = WorksheetFunction.Transpose(ai)
End Sub

Still no sheet posted?

Look at the macro in the section "Collect Values"...you see we I activate each sheet and do the COUNT on that sheet. Every sheet EXCEPT Summary. So, until you post your sheet, I can't guess why the basics aren't working for you. I'm sure it's something simple.

Well, it DOES behave differently than it did on my test sheet. I imagine this is a good a reason as any to start with the worksheet itself, huh?

Borrowing SHG's "string" approach, it behaves as expected.

Sub Search_NotClear()
Dim AL As Long, CL As Long, LT As Long, BH As Long, SL As Long
Dim US As Long, ML As Long, UL As Long, a As Long
Dim ws As Worksheet, Rng As String
Rng = "C8:F68"

'Collect values
    For Each ws In Worksheets
        ws.Activate
        If ws.Name <> "Summary" Then
            AL = AL + WorksheetFunction.CountIf(Range(Rng), "A/L")
            CL = CL + WorksheetFunction.CountIf(Range(Rng), "C/L")
            LT = LT + WorksheetFunction.CountIf(Range(Rng), "L/T")
            BH = BH + WorksheetFunction.CountIf(Range(Rng), "B/H")
            SL = SL + WorksheetFunction.CountIf(Range(Rng), "S/L")
            US = US + WorksheetFunction.CountIf(Range(Rng), "U/S")
            ML = ML + WorksheetFunction.CountIf(Range(Rng), "M/L")
            UL = UL + WorksheetFunction.CountIf(Range(Rng), "U/L")
            a = a + WorksheetFunction.CountIf(Range(Rng), "A")
        End If
    Next ws

'Insert values into Summary
Sheets("Summary").Activate
    Range("A1").Value = AL
    Range("A2").Value = CL
    Range("A3").Value = LT
    Range("A4").Value = BH
    Range("A5").Value = SL
    Range("A6").Value = US
    Range("A7").Value = ML
    Range("A8").Value = UL
    Range("A9").Value = a

End Sub