Need help with a macro

  • Thread starter Thread starter sk8rider
  • Start date Start date
S

sk8rider

Hello there,

I would appreciate any help you guys could give me.

I have a spreadsheet which have 3 labels(alpha, beta, production).
Underneath each label I have two columns (number, resolution). Thes
labels together with the columns are pushed up and down depending o
how many cells are copied underneath of them(non-static labels). No
here is what I am trying to accomplish:

Under the column resolution there are 3 different items (reso, ente
assi) that I would like the count of how many each one of them ther
is. so I would end up with something like this:
2 reso, 4 ente, 0 assi.

The spreadsheet looks like this:

A B
Stopper = Alpha
Number Resolution
8645 ASSI
9994 ASSI
8888 RESO


Stopper = Beta
Number Resolution
3243 ENTE
3555 ENTE
9999 ASSI

Stopper = Production
Number Resolution
9996 RESO

so, i would have an output like this:
Alpha 3 bugs (2 assi, 1 reso)
Beta 3 bugs (2 ente,1 assi)
Production 1 bug (1 reso)

Again, thank for your time and help
 
Your data is in the wrong format to be of much use for analysis. If yo
have a table you will find it easy to use Excel functions and pivo
tables. eg. (in separate cells)-
Alpha 8645 ASSI
Alpha 9994 ASSI
Alpha 8888 RESO
Beta 3243 ENTE
Beta 3555 ENTE
Beta 9999 ASSI
etc
 
Thanks Brian for your reply.

My issue here is that there is so much data that a table would b
messy. I am talking of about 300 entries per section (alpha
beta,production
 
If I were doing it, I have my report numbers in separate cells. Kind of like:

Stopper Total reso ente assi
Alpha 3 1 0 2
Beta 3 0 2 1
Production 1 1 0 0

It might make any next steps easier.

If that's ok, then I think this'll do it:

Option Explicit
Option Base 0
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim FoundCell As Range
Dim TopCell As Range
Dim BotCell As Range
Dim oRow As Long
Dim MyKeys As Variant
Dim myCodes As Variant
Dim iCtr As Long
Dim cCtr As Long

MyKeys = Array("Alpha", "Beta", "Production")
myCodes = Array("reso", "ente", "assi")

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With newWks
.Range("a1").Value = "Stopper"
.Range("B1").Value = "Total"
.Range("c1").Resize(1, 3).Value = myCodes
End With

oRow = 1
With curWks
For iCtr = LBound(MyKeys) To UBound(MyKeys)
With .Range("a:a")
Set FoundCell = .Cells.Find _
(what:="Stopper = " & MyKeys(iCtr), _
after:=.Cells(.Cells.Count), _
lookat:=xlPart, _
MatchCase:=False)

End With
If FoundCell Is Nothing Then
MsgBox MyKeys(iCtr) & " Wasn't found!!!!"
Else
Set TopCell = FoundCell.Offset(2, 1) 'down 2, right 1
Set BotCell = TopCell
If IsEmpty(TopCell.Offset(1, 0)) Then
'don't adjust
'keep it here--just one entry
Else
If IsEmpty(TopCell.Offset(2, 0)) Then
Set BotCell = TopCell.Offset(1, 0)
Else
Set BotCell = TopCell.End(xlDown)
End If
End If
oRow = oRow + 1
With newWks
With .Cells(oRow, 1)
.Value = MyKeys(iCtr)
.Offset(0, 1).Formula _
= "=counta(" & Range(TopCell, _
BotCell).Address(external:=True) & ")"

For cCtr = LBound(MyKeys) To UBound(MyKeys)
.Offset(0, cCtr + 2).Formula _
= "=countif(" & Range(TopCell, _
BotCell).Address(external:=True) & "," _
& Chr(34) & myCodes(cCtr) & Chr(34) & ")"
Next cCtr

End With
End With
End If
Next iCtr
End With

With newWks
.UsedRange.Columns.AutoFit
.Range("B:E").HorizontalAlignment = xlCenter
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top