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.

 

[madhuchelliah]

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?

Hello Rick, Thanks for get back on this. I am importing a xml to my excel. This is the standard format followed in all XMLs. So answer for your question is it is part of the rext. Thanks again.

 

[Peter_SSs]

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

Not sure I fully understand all that but try this version.
If it is still not what you want, please give 5-10 varied examples of the C3 and 'last' values and the expected result for each of the samples.

Sub DeleteFirst_v2()
  Dim rSub As Range

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