Hello Y'all,
I have a spreadsheet with adverse event data. Column A has the adverse event term exactly as the staff member entered it into the database. I am working on creating a report that counts the number of instances that an AE term appears in column A. The issues that I am having trouble with are that there are MANY AE terms, the AE terms are variable (and unpredictable), and the number of rows in the spreadsheet is variable.
I have figured that the easiest, most organized thing to do is to create a new sheet as the destination for the number of occurances. Below is my code with the troble area in red:
Any and all help is appreciated! Thanks!
I have a spreadsheet with adverse event data. Column A has the adverse event term exactly as the staff member entered it into the database. I am working on creating a report that counts the number of instances that an AE term appears in column A. The issues that I am having trouble with are that there are MANY AE terms, the AE terms are variable (and unpredictable), and the number of rows in the spreadsheet is variable.
I have figured that the easiest, most organized thing to do is to create a new sheet as the destination for the number of occurances. Below is my code with the troble area in red:
Code:
Sub RM2004_AE_Count()
Dim myrows As Integer
Dim i As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws2row As Integer
Dim LRow As Long
Dim Drng As Range
Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Range("1:1").Delete xlShiftUp
With ws1
.Name = "AE Count Source Data"
.Range("A1") = [{"Adverse Event Term"}]
.Columns("A:L").Sort key1:=.Range("A2"), order1:=xlAscending, Header:=xlYes
End With
With ws1.Columns("A:L").Resize(ws1.UsedRange.Rows.Count)
.AutoFilter 1, ""
With .Offset(1).SpecialCells(xlCellTypeVisible)
.EntireRow.Delete
End With
.AutoFilter
End With
Set ws2 = ActiveWorkbook.Worksheets.Add(After:=ws1)
ws2.Name = "MedRA UTR Report"
ws2row = 1
ws2.Cells(ws2row, 1) = "Verbatim"
ws2.Cells(ws2row, 2) = "Term Text"
ws2.Cells(ws2row, 3) = "Term Type"
ws2.Cells(ws2row, 4) = "Term Code"
ws2.Cells(ws2row, 5) = "Count"
ws2.Cells(ws2row, 6) = "PT"
ws2.Cells(ws2row, 7) = "PT Code"
ws2.Cells(ws2row, 8) = "HLT"
ws2.Cells(ws2row, 9) = "HLT Code"
ws2.Cells(ws2row, 10) = "HLGT"
ws2.Cells(ws2row, 11) = "HLGT Code"
ws2.Cells(ws2row, 12) = "SOC"
ws2.Cells(ws2row, 13) = "SOC Code"
ws2.Cells(ws2row, 14) = "Version"
ws1.Select
LRow = Cells(Rows.Count, "A").End(xlUp).Row
Do While LRow > 0
[COLOR=#ff0000]If Cells(LRow, 1).Value = Cells(LRow - 1, 1).Value Then
ws2.Range("E" & ws2.Rows.Count).End(xlUp).Offset(1) = CountIf(Range("A:A", Cells(LRow, 1).Value)
End If
LRow = LRow - 1
[/COLOR]Loop
End SubAny and all help is appreciated! Thanks!
