broman5000
01-12-2017, 05:19 AM
Trying to figure this one out...Here's what i'd like to do (current code and excel example is below)
The code runs just fine for my sheet, but I don't know how to add one more aspect
While running the loop, if anywhere in column B there is the text "PNP", then I'd like to delete the cell and shift up the column B cell and the corresponding column C cell as they are not needed
I want to keep column A intact as it is the master and is updated by a different organization
Open to any suggestions...thanks!
Master Unique Indicator As Of P201701
Weekly Run Unique Identifier
Weekly Match To Master?
F38/060881026 9:30
PNP/00023200513:00
Match Not Found!
F50/003510586 5:15
PNP/00027705013:00
Match Not Found!
M12/00695151710:30
PNP/00088603513:00
Match Not Found!
PNP/72027706113:00
M93/010270903 9:00
Match Not Found!
PNP/79588601113:00
M96/00265045114:00
match
R01/006277104 9:00
M93/00465441214:00
match
Sub routechange2()
Dim CompareRange As Variant, To_Be_Compared As Variant, x As Long, y As Long, i As Long, alr As Long, blr As Long
Sheets("Unique Identifier list").Select
alr = Sheets("Unique Identifier list").Cells(Rows.Count, "A").End(xlUp).Row
blr = Sheets("Unique Identifier list").Cells(Rows.Count, "B").End(xlUp).Row
To_Be_Compared = Range("B1:B" & blr)
CompareRange = Range("A1:A" & alr)
For x = 2 To blr
For y = 2 To alr
If To_Be_Compared(x, 1) = CompareRange(y, 1) Then
Range("C2").Offset(x - 2, 0).Value = "match"
End If
Next y
Next x
For x = 2 To blr
For y = 2 To alr
If Range("C2").Offset(x - 2, 0).Value = "" Then
Range("C2").Offset(x - 2, 0).Value = "Match Not Found!"
End If
Next y
Next x
Range("C1").Value = "Weekly Match To Master?"
End Sub
The code runs just fine for my sheet, but I don't know how to add one more aspect
While running the loop, if anywhere in column B there is the text "PNP", then I'd like to delete the cell and shift up the column B cell and the corresponding column C cell as they are not needed
I want to keep column A intact as it is the master and is updated by a different organization
Open to any suggestions...thanks!
Master Unique Indicator As Of P201701
Weekly Run Unique Identifier
Weekly Match To Master?
F38/060881026 9:30
PNP/00023200513:00
Match Not Found!
F50/003510586 5:15
PNP/00027705013:00
Match Not Found!
M12/00695151710:30
PNP/00088603513:00
Match Not Found!
PNP/72027706113:00
M93/010270903 9:00
Match Not Found!
PNP/79588601113:00
M96/00265045114:00
match
R01/006277104 9:00
M93/00465441214:00
match
Sub routechange2()
Dim CompareRange As Variant, To_Be_Compared As Variant, x As Long, y As Long, i As Long, alr As Long, blr As Long
Sheets("Unique Identifier list").Select
alr = Sheets("Unique Identifier list").Cells(Rows.Count, "A").End(xlUp).Row
blr = Sheets("Unique Identifier list").Cells(Rows.Count, "B").End(xlUp).Row
To_Be_Compared = Range("B1:B" & blr)
CompareRange = Range("A1:A" & alr)
For x = 2 To blr
For y = 2 To alr
If To_Be_Compared(x, 1) = CompareRange(y, 1) Then
Range("C2").Offset(x - 2, 0).Value = "match"
End If
Next y
Next x
For x = 2 To blr
For y = 2 To alr
If Range("C2").Offset(x - 2, 0).Value = "" Then
Range("C2").Offset(x - 2, 0).Value = "Match Not Found!"
End If
Next y
Next x
Range("C1").Value = "Weekly Match To Master?"
End Sub