My code consists of Split
, Mid
, Left
, Right
and Replace
functions which are used to trim my data into a more neat manner. However, it's too long and inefficient, it takes too much time to go through all this because I have up to thousands of files that will go through this.
My main code goes through a folder and goes through all the files inside the folder to find and search for strings and then copy it out into a new worksheet.
'Test RowA(1)
wks.Cells(BlankRow, 1).Replace What:="testtest : ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'Start RowB(2)
wks.Cells(BlankRow, 2).Replace What:=" Started at: ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'Temp RowC(3)
Dim strSearchC As String
Dim cCell As Range
Dim s3 As String
strSearchC = "testflyy"
Set cCell = wks.Cells(BlankRow, 3).Find(What:=strSearchC, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not cCell Is Nothing Then
s3 = Split(cCell.Value, ":")(1)
s3 = Mid(s3, 1, 3)
cCell.Value = s3
End If
'Type RowD(4)
Dim strSearchD As String
Dim dCell As Range
Dim s4 As String
strSearchD = "testflyy"
Set dCell = wks.Cells(BlankRow, 4).Find(What:=strSearchD, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not dCell Is Nothing Then
s4 = Split(dCell.Value, "_", 3)(2)
s4 = Mid(s4, 1, 3)
dCell.Value = s4
End If
'No RowF(6)
Dim strSearchF As String
Dim fCell As Range
Dim s6 As String
strSearchF = "homebeestrash_archivetestts"
Set fCell = wks.Cells(BlankRow, 6).Find(What:=strSearchF, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not fCell Is Nothing Then
s6 = Split(fCell.Value, "testts")(1)
s6 = Mid(s6, 1, 2)
fCell.Value = s6
End If
'End RowG(7)
Dim strSearchG As String
Dim gCell As Range
Dim s7 As String
strSearchG = "homebeestrash_archivetestts"
Set gCell = wks.Cells(BlankRow, 7).Find(What:=strSearchG, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not gCell Is Nothing Then
s7 = Split(gCell.Value, "reports")(1)
s7 = Split(s7, "Report")(0)
s7 = Left(s7, 8) & " " & Right(s7, 6)
'Month RowH(8)
wks.Cells(BlankRow, 8).Value = WorksheetFunction.Transpose(Left(s7, 4) & "-" & Mid(s7, 5, 2) & "-" & Mid(s7, 7, 2)) '<~~ get 2019-03-02
gCell.Value = s7
End If
Basically these are to trim the already extracted data in each respective rows worksheet, Row A and B is just simply replacing the data with nothing, for Row C onwards to Row H,, i will search if the string exists on the particular row and then use Split
or Right
, Left
, Mid
functions.
Question: Is there any other way to write it more efficiently because there are too many codings with formats like this and I feel like its slowing my program down a lot.