Advanced Find and Replace VBA

[madhuchelliah]

Hello all, i have to find a particular characters from the string and it has to be replaced with none. I have string which is combination of letter, symbols, spaces and numbers in I column. From the cell I4 the code has to read the content before the first instance of "-" high-fun and replace that content with none in all the cells starting with that content including I4. Please go through the example for better understanding. In I4 before the first instance of the high fun, the content is 25 B. I8 also starting with similar content. The code has to read that 25 B and delete from both the cells I4 and I8. The column range is dynamic and the content might be available at nth times. Thank you

	I
1	XXXXX AAAA 645A-sd
2
3	YYYY
4	25 B -1 XXX SSSSS 236-235
5	02 ghfgdhfg fdfdd fdfd
6	50 ds ssdss sdsdsds
7	-50A ff ff
8	25 B 25 sdfsf fssdfs sfsf
9	sd sdsds sdsds*ssd
10	hgfhfh / gdf + dff5

 

[DanteAmor]

Try this

Sub Find_Replace()
  Dim a As Variant, s As String
  Dim i As Long
  a = Range("I4", Range("I" & Rows.Count).End(3)).Value2
  
  For i = 1 To UBound(a)
    If InStr(1, a(i, 1), "-") > 0 And s = "" Then
      s = WorksheetFunction.Trim(Split(a(i, 1), "-")(0))
    End If
    If s <> "" And Left(a(i, 1), Len(s)) = s Then
      a(i, 1) = Mid(a(i, 1), Len(s) + 1)
    End If
  Next
  Range("I4").Resize(UBound(a)).Value = a
End Sub

 

[madhuchelliah]

Try this

Sub Find_Replace()
  Dim a As Variant, s As String
  Dim i As Long
  a = Range("I4", Range("I" & Rows.Count).End(3)).Value2
 
  For i = 1 To UBound(a)
    If InStr(1, a(i, 1), "-") > 0 And s = "" Then
      s = WorksheetFunction.Trim(Split(a(i, 1), "-")(0))
    End If
    If s <> "" And Left(a(i, 1), Len(s)) = s Then
      a(i, 1) = Mid(a(i, 1), Len(s) + 1)
    End If
  Next
  Range("I4").Resize(UBound(a)).Value = a
End Sub

Hello Dan, thanks for your solution. It is working as expected. But a small change in the requirement. As per old requirement, the content has to be replaced by none in any available cell.Instead of that, the content should be replaced only if the above cell of the content starts with "Date". Please see the example for better understanding. For Cells I4, I8 and I16, the above cell is start with date so it has to be replaced the content with none. For cell I11 th above cell is not starts with date so it has to be untouched. Sorry for the inconvenience. Thanks again. Stay safe.

Book4.xlsm
	I
1	XXXXX AAAA 645A-sd
2	ererree erer/* *595
3	Date. Sds dsdsd dsds@=*
4	25 B -1 XXX SSSSS 236-235
5	02 ghfgdhfg fdfdd fdfd
6	50 ds ssdss sdsdsds
7	Date. Sssd sdsdds
8	25 B 25 sdfsf fssdfs sfsf
9	sd sdsds sdsds*ssd
10	hgfhfh / gdf + dff5
11	25 B 25 sdfsf fssdfs sfsf
12	sd sdsds sdsds*ssd
13	hgfhfh / gdf + dff5
14	02 ghfgdhfg fdfdd fdfd
15	Date. 50 ds ssdss sdsdsds
16	25 B 25 sdfsf fssdfs sfsf
Sheet1

 

[DanteAmor]

Try this

Sub Find_Replace()
  Dim a As Variant, s As String
  Dim i As Long
  a = Range("I3", Range("I" & Rows.Count).End(3)).Value2
  
  For i = 2 To UBound(a)
    If InStr(1, a(i, 1), "-") > 0 And s = "" Then
      s = Split(a(i, 1), "-")(0)
    End If
    If s <> "" And Left(a(i, 1), Len(s)) = s And Left(a(i - 1, 1), 4) = "Date" Then
      a(i, 1) = Mid(a(i, 1), Len(s) + 1)
    End If
  Next
  Range("I3").Resize(UBound(a)).Value = a
End Sub

 

[madhuchelliah]

Try this

Sub Find_Replace()
  Dim a As Variant, s As String
  Dim i As Long
  a = Range("I3", Range("I" & Rows.Count).End(3)).Value2
 
  For i = 2 To UBound(a)
    If InStr(1, a(i, 1), "-") > 0 And s = "" Then
      s = Split(a(i, 1), "-")(0)
    End If
    If s <> "" And Left(a(i, 1), Len(s)) = s And Left(a(i - 1, 1), 4) = "Date" Then
      a(i, 1) = Mid(a(i, 1), Len(s) + 1)
    End If
  Next
  Range("I3").Resize(UBound(a)).Value = a
End Sub

Hey Dan, that's super cool. Working great !!!. Thank you. Stay safe.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.