hi,
excel 2010 windows 7
i want to read from text files in one directory the total of the numbers to the right of "From Data" from all cycles dated 01-Sep-2015
if user input "01-Sep-2015" then the function return total of all numbers to the right of "from data" from all cycles with date 01-Sep-2015 in this data sample answer 17+18=35
the text files can contain multiple cycles like 1-sep-2015 2-sep-2015 and on ....
sample data attached.
this my code
the function return #invalid!
excel 2010 windows 7
i want to read from text files in one directory the total of the numbers to the right of "From Data" from all cycles dated 01-Sep-2015
if user input "01-Sep-2015" then the function return total of all numbers to the right of "from data" from all cycles with date 01-Sep-2015 in this data sample answer 17+18=35
the text files can contain multiple cycles like 1-sep-2015 2-sep-2015 and on ....
sample data attached.
this my code
Code:
Function FSCount(CyDA As String) As Integer
Dim myfile As String
Dim FileName As String
Dim FrDA As Integer
folderName = Cells(2, 11).Value
FileName = Format(DateSerial(2000 + Left(CyDA, 2), Mid(CyDA, 3, 2), Right(CyDA, 2)), "yyyy_mmm")
i = 1
myfile = Dir(folderName & "\FS_QC_Count_Log_" & "*.txt")
Do While myfile <> ""
Open folderName & "\" & myfile For Input Access Read As #1
Do While Not EOF(1)
Line Input #1, EntireLine
SearchKey = "Cycle Date"
SearchKey2 = "From Data"
If CyDA = InStr(1, EntireLine, Right(SearchKey, 11)) Then
FrDA = Trim(InStr(1, EntireLine, Right(SearchKey2, 10), 1))
End If
Close #1
Loop
Loop
End Function