[RESOLVED] Analyzing an Excel worksheet.

[eranga262154]

Hi all,

I've an excel sheet of 3 columns. I want to collect some information like follows. Basically count occurrence of a range.

=COUNTIF(B:B, "<100")
=COUNTIF(B:B, "<200")
=COUNTIF(B:B, "<300")
=COUNTIF(B:B, "<500")
=COUNTIF(B:B, "<700")
=COUNTIF(B:B, "<1000")
=COUNTIF(B:B, "<1500")
=COUNTIF(B:B, "<2000")

something like that. May be you are not clear, on column B I have some values. I want to calculate each count as above and put them in cells.

Can anyone give me a solution. Doing it manually is a real mess.

[Siddharth Rout]

Sub CountOccurances()
    '~~> Depending upon your conditions change this
    Dim Ar(7) As String
    Ar(0) = "<100"
    Ar(1) = "<200"
    Ar(2) = "<300"
    Ar(3) = "<500"
    Ar(4) = "<700"
    Ar(5) = "<1000"
    Ar(6) = "<1500"
    Ar(7) = "<2000"
    
    '~~> Storing the formula, starting from C1
    For i = 0 To UBound(Ar)
        Sheets("Sheet1").Range("C" & i + 1).Value = _
        "=COUNTIF(B:B, " & """" & Ar(i) & """" & ")"
    Next
End Sub

[eranga262154]

Thanks, actually I've solve the question with macros. Thanks for the comment lol.