I developed a script for the company I work for in order to extract data from SAP (CJ74) and analyze the data.
One particular issue I am finding is that for any data sets which have over 1000 lines, it struggles to execute the section of code below, therefore I am searching for answers.
This code takes free hand text from cells in Column J into an array, it then splits the text up and tries to find matches to first and last names located in Column F and G.
This works functionally but it runs like a slug with data sets over 1000 lines, and potentially crashes the program if there are data sets which are at 3000 - 4000 lines and above. Why would this be the case? Have I coded this inefficiently or is this one of VBA's gotchas? If so would there be a better way to write this code?
Option Compare Text
Option Explicit
Sub Loader()
Dim I As Long, J As Long
Dim T As Variant
Dim match_txt As String
'** Takes text from Column J and wraps it into an array**
For I = 2 To Range("E50000").End(xlUp).row
typ = Range("F" & I).Value
If typ = "" Then
txt = Range("J" & I).Value
'** Split array into separate lines **
txtArray = Split(txt, " ")
'** Check Column F for matches, if it finds a match, put the matching text into column F under the assumption that it is a first name **
For Each T In txtArray
For J = 2 To Range("F50000").End(xlUp).row
If Range("F" & J).Value = T Then
match_txt = T
Range("F" & I).Value = match_txt
End If
Next J
Next T
'** Check Column G for matches, if it finds a match, put the matching text into column G under the assumption that it is a last name **
For Each T In txtArray
For J = 2 To Range("F50000").End(xlUp).row
If Range("G" & J).Value = T Then
match_txt = T
Range("G" & I).Value = match_txt
End If
Next J
Next T
End If
Next I
End Sub()