PDA

View Full Version : Loop for certain text in cell - delete cells and shift up



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

offthelip
01-12-2017, 04:48 PM
I recognise this code from your last thread
Here you are try this:
you will have to work out what the types are, it is good for your education.

Sub routechange3()
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
clr = Application.Max(alr, blr)

To_Be_Compared = Range("B1:B" & blr)
CompareRange = Range("A1:A" & alr)
' Define and intialise output array
C_output = Range("b1:C" & clr)
For i = 2 To clr
C_output(i, 1) = ""
C_output(i, 2) = ""
Next i

Z = 2

For x = 2 To blr
'check if first three characters are PNP if so skip
Threechars = Left(To_Be_Compared(x, 1), 3)
If Not (Threechars = "PNP") Then
C_output(Z, 1) = To_Be_Compared(x, 1)
Z = Z + 1
For y = 2 To alr
If To_Be_Compared(x, 1) = CompareRange(y, 1) Then
C_output(Z - 1, 2) = "match"
End If
Next y
End If
Next x


For x = 2 To Z - 1
If C_output(Z, 2) = "" Then
If Not (C_output(Z, 1) = "") Then
C_output(Z, 2) = "Match Not Found!"
End If
End If
Next x

Range(Cells(1, 2), Cells(clr, 3)) = C_output

Range("C1").Value = "Weekly Match To Master?"








End Sub

broman5000
01-13-2017, 06:27 AM
appreciate your response...what do you mean by types?

broman5000
01-13-2017, 06:33 AM
nevermind :)