Nick72310
01-26-2016, 10:12 AM
I am trying to use a macro to filter data that I have. I want all items starting the characters TEMP and CR- to be filtered. I have working code below, but it has a few issues. I have items starting with the characters SCR-, ZZZ_CR- and ZZZ_Temp, and they get filtered into my list as well. I don't want them to. I only want items with the initial 4 characters being TEMP and initial 3 characters being CR-.
Also, in my code, I have the variable j. I want j to equal the amount of rows I have in the named array "ITEM_NUMBER". I'm not sure how to do that.
If I could get the corresponding lists to be named TEMP_FILTER & CR_FILTER, that would be GREAT as well!
Thank you in advance!
Sub Filter()
'On Error Resume Next
Dim i
t = 1
c = 1
'j = WorksheetFunction.Rows([ITEM_NUMBER]).Value
j = 1000000
For i = 1 To j
'TEMP
If InStr(1, Worksheets("Oracle Item Numbers").Cells(1 + i, 1).Value, "TEMP", 1) Then
Worksheets("Templates & Common Routings").Cells(1 + t, 10).Value = Worksheets("Oracle Item Numbers").Cells(1 + i, 1).Value
t = t + 1
End If
'CR-
If InStr(1, Worksheets("Oracle Item Numbers").Cells(1 + i, 1).Value, "CR-", 1) Then
Worksheets("Templates & Common Routings").Cells(1 + c, 11).Value = Worksheets("Oracle Item Numbers").Cells(1 + i, 1).Value
c = c + 1
End If
Next i
End Sub
Also, in my code, I have the variable j. I want j to equal the amount of rows I have in the named array "ITEM_NUMBER". I'm not sure how to do that.
If I could get the corresponding lists to be named TEMP_FILTER & CR_FILTER, that would be GREAT as well!
Thank you in advance!
Sub Filter()
'On Error Resume Next
Dim i
t = 1
c = 1
'j = WorksheetFunction.Rows([ITEM_NUMBER]).Value
j = 1000000
For i = 1 To j
'TEMP
If InStr(1, Worksheets("Oracle Item Numbers").Cells(1 + i, 1).Value, "TEMP", 1) Then
Worksheets("Templates & Common Routings").Cells(1 + t, 10).Value = Worksheets("Oracle Item Numbers").Cells(1 + i, 1).Value
t = t + 1
End If
'CR-
If InStr(1, Worksheets("Oracle Item Numbers").Cells(1 + i, 1).Value, "CR-", 1) Then
Worksheets("Templates & Common Routings").Cells(1 + c, 11).Value = Worksheets("Oracle Item Numbers").Cells(1 + i, 1).Value
c = c + 1
End If
Next i
End Sub