VBA locate & delete words within a sentence
Solved/Closed
Related:
- Block if without end if
- Vba case like - Guide
- Number to words in excel formula without vba - Guide
- Delete my whatsapp account without app - Guide
- How to delete whatsapp account without phone - Guide
- How to delete snapchat account - Guide
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Aug 4, 2011 at 12:17 AM
Aug 4, 2011 at 12:17 AM
copy the string in B3 to M3 ( can be any empty cell in that case modify macro "undo"
try this macro test
the spellings in B3, M3 and in the macro should be exact -no unnecessary spaces
try this macro test
the spellings in B3, M3 and in the macro should be exact -no unnecessary spaces
Sub test() Dim r As Range, j As Integer On Error Resume Next Set r = Range("B3") j = WorksheetFunction.Search("for", r) r.Value = Left(r, j - 1) & Right(r, Len(r) - (j + 3)) j = WorksheetFunction.Search("parish", r) r.Value = Left(r, j - 2) j = WorksheetFunction.Search("new plymouth", r) If IsNumeric(j) Then r.Value = Left(r, j - 1) & "np" End If End Sub
Sub undo() Range("M3").Copy Range("B3") End Sub
Aug 4, 2011 at 04:24 PM
I also just realised I need to do the same with "Palmerston North" replacing it with "Palm.N"
I tried this but it came up with a compile error - Block If without End If...
Sub test()
Dim r As Range, j As Integer
On Error Resume Next
Set r = Range("B3")
j = WorksheetFunction.Search("for", r)
r.Value = Left(r, j - 1) & Right(r, Len(r) - (j + 3))
j = WorksheetFunction.Search("parish", r)
r.Value = Left(r, j - 2)
j = WorksheetFunction.Search("new plymouth", r)
If IsNumeric(j) Then
r.Value = Left(r, j - 1) & "np"
j = WorksheetFunction.Search("palmerston north", r)
If IsNumeric(j) Then
r.Value = Left(r, j - 1) & "Palm.N"
End If
End Sub
Aug 4, 2011 at 04:26 PM
Aug 4, 2011 at 04:34 PM
Aug 4, 2011 at 04:48 PM
Aug 4, 2011 at 04:56 PM