Advanced Find and Replace Required

[madhuchelliah]

Hello Guys, I have two scenarios to workout. Both are almost similar only small differences. I am using simple find and replace for this, but now i am having new issues. Please go through the following scenarios.

Scenario 1

The macro should read the cell C3 and delete that value in the last used row of C column. The last used row is always a string. Here i am using normal find and replace macros but now the problem is sometimes the string has the value of C3 multiple times. But i want to delete the last instance(Bold Value) or 1st instance from right side of the value in the string. See the example below.

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]YYY[/TD]
[TD="align: center"]YYY[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]25B[/TD]
[TD="align: center"]25B[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]YYY[/TD]
[TD="align: center"]YYY[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]ZZZ[/TD]
[TD="align: center"]ZZZ[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]25B 250 25B 25 25B[/TD]
[TD="align: center"]25B 250 25B 25[/TD]
[/TR]
</tbody>[/TABLE]


Scenario 2

This one is pretty similar to the 1st one. The macro should read the contents before the minus symbol in the string of cell C3 and remove that content from 1st instance of the string in last used row of C column. See the example below.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]XXX[/TD]
[TD="align: center"]XXX[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]YYY[/TD]
[TD="align: center"]YYY[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]25 B -1[/TD]
[TD="align: center"]25 B -1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]YYY[/TD]
[TD="align: center"]YYY[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]ZZZ[/TD]
[TD="align: center"]ZZZ[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]AAA[/TD]
[TD="align: center"]AAA[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]25 B 100 25 B 105 110[/TD]
[TD="align: center"]100 25 B 105 110[/TD]
[/TR]
</tbody>[/TABLE]


I need 2 different macros for the two scenarios. I am not an expert to solve this. Please anyone heads up. Thank you.

 

[jim may]

These will get you a distance down the road... Apply these 2 Macros against a Backup Copy of your Files ONLY...

Sub FirstMacro()
lr = Range("C" & Rows.Count).End(xlUp).Row
fValue = Range("C3").Value
CharPos = InStrRev(Range("C" & lr).Value, fValue, , 1)
Range("C" & lr).Value = Left(Range("C" & lr).Value, CharPos - 2)
End Sub

Sub SecondMacro()
lr = Range("C" & Rows.Count).End(xlUp).Row
Neg = InStr(1, Range("C3").Value, "-", 1)
If Neg > 0 Then
fValue = Left(Range("C3").Value, Len(Range("C3").Value) - 3)
Else
MsgBox "No Negs"
Exit Sub
End If
lValue = Len(fValue)
Range("C" & lr).Value = Mid(Range("C" & lr).Value, lValue + 2, 99)
End Sub

 

[Rick Rothstein]

You do not actually need two separate macros as a single macro can be constructed that takes the correct action depending on whether cell C3 contains a dash or not. Here is such a macro...

[table="width: 500"]
[tr]
	[td]Sub ComboMacro()
  If [C3] Like "*-*" Then
    [C6] = Application.Trim(Replace([C6], [TRIM(LEFT(C3,FIND("-",C3)-1))], "", , 1))
  ElseIf [C6] Like "*" & [C3] & "*" Then
    [C6] = Evaluate("TRIM(SUBSTITUTE(C6,TRIM(C3),""""," & UBound(Split([C6], [TRIM(C3)])) & "))")
  End If
End Sub[/td]
[/tr]
[/table]

 

[Peter_SSs]

@ Jim & Rick
Could be wrong but my reading for the first problem and given value in C3, anf if the following value was in C6, then the replacement should be from the blue term, not from the red term which your codes act (differently) on.
25B 250 25B 25 2425BC

Rick, further
- yours does not necessarily act on the "last used row in column C"
- again I do not know for sure, but I question the assertion that the macro can decide the course of action. Might not the OP want to delete the last instance of a C3 code and that code could still happen to include a "-"?

@ madhuchelliah
Here are my suggestions for your two codes. However, I was a little unsure about the second one. In your example, C3 contains "25 B -1" so before the "-" is "25 B " which ends with a space character. If your bottom column C cell contained "28 C 100 25 B" should any replacement be made given that the cell does not contain any instance of "25 B "?

If Rick's assumption that the particular replacement (first v last) can be determined by the existence or not of a "-" in C3, then my two codes could also be combined into one if you want.

First problem

Sub DeleteLast()
  Dim rSub As Range
  
  Set rSub = Range("C" & Rows.Count).End(xlUp)
  With CreateObject("VBScript.RegExp")
    .Pattern = Replace("\b#\b(?!.*\b#\b)", "#", Range("C3").Value)
    rSub.Value = Application.Trim(.Replace(rSub.Value, ""))
  End With
End Sub

Second problem

Sub DeleteFirst()
  Dim rSub As Range
  
  Set rSub = Range("C" & Rows.Count).End(xlUp)
  With CreateObject("VBScript.RegExp")
    .Pattern = "\b" & Split(Range("C3").Value, "-")(0) & "\b"
    rSub.Value = Application.Trim(.Replace(rSub.Value, ""))
  End With
End Sub

 

[madhuchelliah]

Thank you all experts for spare your time. Finally i picked Peter's solution. Thanks again Jim, Rick and Peter.

 

[Peter_SSs]

Thanks again Jim, Rick and Peter.

You are very welcome.

 

[madhuchelliah]

Second problem

Sub DeleteFirst()
  Dim rSub As Range
  
  Set rSub = Range("C" & Rows.Count).End(xlUp)
  With CreateObject("VBScript.RegExp")
    .Pattern = "\b" & Split(Range("C3").Value, "-")(0) & "\b"
    rSub.Value = Application.Trim(.Replace(rSub.Value, ""))
  End With
End Sub

Hello Peter, I am facing a small issue. If the string contains minus symbol the code is not running. For example if the string is 25 B -100 25 B 105 110 then the result should be -100 25 B 105 110, but the code is not giving the result. Please look into it. Thank you.

 

[Peter_SSs]

Hello Peter, I am facing a small issue. If the string contains minus symbol the code is not running. For example if the string is 25 B -100 25 B 105 110 then the result should be -100 25 B 105 110, but the code is not giving the result. Please look into it. Thank you.

I can see why my code is doing that. However, before considering the best modification, I refer back to an earlier question of mine which was not answered. An answer to that would be helpful.

However, I was a little unsure about the second one. In your example, C3 contains "25 B -1" so before the "-" is "25 B " which ends with a space character. If your bottom column C cell contained "28 C 100 25 B" should any replacement be made given that the cell does not contain any instance of "25 B "?

.. and now I have a further question about the second problem. Is there always a space immediately before the "-" in C3? If not, I may have some more follow-up questions.

 

[madhuchelliah]

I can see why my code is doing that. However, before considering the best modification, I refer back to an earlier question of mine which was not answered. An answer to that would be helpful.


.. and now I have a further question about the second problem. Is there always a space immediately before the "-" in C3? If not, I may have some more follow-up questions.

Hello peter, answer for the 1st question, the last cell of the column always contains the value before the minus symbol of C3 cell as first instance. So no need of any replacement. Answer for you second question, yes always there will be a space before minus symbol. The last cell will be with or without minus symbol

 

[Rick Rothstein]

Answer for you second question, yes always there will be a space before minus symbol. The last cell will be with or without minus symbol

Just clarifying... is that space part of the text to be found or is the space just a separator between the text you want to find and the minus sign?