I am working on a code that I am having issues with and I believe it is with my instr criteria.
In addition I would like to also capture the two rows above the true instr statement and one row below in order to keep everything in context on the output sheet.
I have a spreadsheet that that I update each day that is usually around 10K to 16K rows. The rows have many columns but in column 38 I run code and if the data is true based on the criteria it will place a string starting with "L-" along with some random numbers in the cell and then it will color the entire row to draw attention to the user of that critical information.
In this daily data set there are usually about 30 rows of data that will match certain criteria and insert into column 38 the "L-" coding.
Raw data is in a sheet called ("R-2.25")
My code below takes this entire sheet and dumps it into an array. All this seems to be fine to this point.
I now want it to iterate through this array looking at column 38 for this "L-" string followed by the various random numbers. As it finds a true row it will dump that data to the "L_Review" sheet.
Plot twist. After resolving my instr search method I would like to grab the 3 rows above and 2 rows below to keep everything in context during the review process.
Here is the code I have thus far.
Thank you for the assistance
In addition I would like to also capture the two rows above the true instr statement and one row below in order to keep everything in context on the output sheet.
I have a spreadsheet that that I update each day that is usually around 10K to 16K rows. The rows have many columns but in column 38 I run code and if the data is true based on the criteria it will place a string starting with "L-" along with some random numbers in the cell and then it will color the entire row to draw attention to the user of that critical information.
In this daily data set there are usually about 30 rows of data that will match certain criteria and insert into column 38 the "L-" coding.
Raw data is in a sheet called ("R-2.25")
My code below takes this entire sheet and dumps it into an array. All this seems to be fine to this point.
I now want it to iterate through this array looking at column 38 for this "L-" string followed by the various random numbers. As it finds a true row it will dump that data to the "L_Review" sheet.
Plot twist. After resolving my instr search method I would like to grab the 3 rows above and 2 rows below to keep everything in context during the review process.
Here is the code I have thus far.
Thank you for the assistance
VBA Code:
Sub l_LoopArray()
' loads entire sheet into an array
' It will clear the dump sheet first
' rw = row counter (aka "i")
' rprw is short for report row starting place
Dim startTime As Double
Dim secondsElapsed As Double
Sheets("L_Review").Cells.Clear
oarray = Sheets("R-2.25").Cells(1, 1).CurrentRegion
startTime = Timer
rprw = 2
For rw = 2 To UBound(oarray)
If oarray(rw, 38) = InStr(Cells(rw, 38).Value, "L-") Then
For cl = 1 To UBound(oarray, 2)
Sheets("L_Review").Cells(rprw, cl) = oarray(rw, cl)
Next
rprw = rprw + 1
End If
Next
secondsElapsed = Round(Timer - startTime, 5)
MsgBox " Rows Processed in " & secondsElapsed & " Seconds", vbInformation
End Sub
