Option Explicit
Sub EinAnfang()
Dim csvFile As String
Dim Path As String
Dim WsCsv As Worksheet
Dim csvColumnDataType() As Variant
Dim csvData As Variant
Dim csvZeile As Long, csvSpalte As Long
Dim i As Long, iSpalte As Long, iZeile As Long, nZeilen As Long
Dim dict As Object
Dim myData As Variant
ReDim myData(1 To 200000, 1 To 4)
myData(1, 1) = "GENE": myData(1, 2) = "silent"
myData(1, 3) = "Missense": myData(1, 4) = "Unknown"
nZeilen = 1
Set dict = CreateObject("Scripting.Dictionary")
csvSpalte = 2
ReDim csvColumnDataType(0 To 200)
For i = 0 To 200: csvColumnDataType(i) = Array(i, 1): Next
Path = ThisWorkbook.Path & "\"
csvFile = Dir(Path & "*.csv")
Do While csvFile <> ""
Workbooks.OpenText Filename:=Path & csvFile, _
Origin:=xlWindows, _
StartRow:=2, _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=csvColumnDataType, _
DecimalSeparator:="."
Set WsCsv = ActiveWorkbook.Sheets(1)
csvData = WsCsv.UsedRange.Value
For csvZeile = 1 To UBound(csvData)
iSpalte = 0
If InStr(LCase(csvData(csvZeile, csvSpalte)), "silent") Then
iSpalte = 2
ElseIf InStr(LCase(csvData(csvZeile, csvSpalte)), "missense") Then
iSpalte = 3
ElseIf InStr(LCase(csvData(csvZeile, csvSpalte)), "unknown") Then
iSpalte = 4
Else
End If
If iSpalte > 0 Then
If dict.exists(csvData(csvZeile, 1)) Then
iZeile = dict(csvData(csvZeile, 1))
Else
nZeilen = nZeilen + 1
dict.Add csvData(csvZeile, 1), nZeilen
iZeile = nZeilen
myData(iZeile, 1) = csvData(csvZeile, 1)
End If
myData(iZeile, iSpalte) = myData(iZeile, iSpalte) + 1
End If
Next
WsCsv.Parent.Close savechanges:=False
csvFile = Dir()
Loop
With ThisWorkbook.Worksheets.Add
.Range("A1").Resize(nZeilen, 4).Value = myData
End With
End Sub