Solved: Delete specific word from rows, add specific words to others

[fwawergurl16]

Hi. I've 2 questions.

A. Is there a way to delete/remove specific words from hundreds of rows in a worksheet?

For example, in row A7:
SL det(conc)-MaxKleen 9
I want it to appear as:
MaxKleen 9

In row A11:
SL det(liq)-Dynamo
I want it to appear as:
Dynamo

I reckon the logic will be that the macro:
1. runs row by row from row7 until the last row (loop)
2. probably has an array of words that should be removed
3. scans the words in each row
4. removes the words like "SL det(conc)-" or "SL det(lit)-" etc.

I found codes that remove the entire row, but I don't want that to happen for my document. Could anyone help here?

B. Is there also a way to add words to specific places of the document?
For example:
SL det(liq)-Dynamo
SL det(liq)-Kuat Harimau
SL det(liq)-Others Liq

add one row above these rows & print word "Liquid" so that it'll look like:
Liquid (if liq, then "Liquid", if conc, then "Concentrated")
Dynamo
Kuat Harimau
Others Liq

Any help would be much appreciated!! Included is a sample doc. Thanks!

[anandbohra]

you can simply find that word & replace with nothing
that will solve your purpose & also as u r doing with excel (without macro) u can anytime revert back through Undo (Ctrl + z)

& if that solve your purpose then u can record the same & do specific change to work on either selection or on entire sheet

[fwawergurl16]

anandbohra,

Good idea! I tried that already. The only thing is, how to get it do in a loop? Meaning it will scan thru the entire doc...the code that i thought of is something like the below, but haha, it doesn't work. The bulk of codes are from the recorded.

[VBA]
Sub findLiquid()

For i = 0 To 65535
Cells.Find(What:="SL det(liq)-", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="SL det(conc)-", Replacement:="", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'Cells.FindNext(After:=ActiveCell).Activate
Next Cells.FindNext(After:=ActiveCell).Activate 'im seriously not sure of this part

End Sub

[/VBA]

[anandbohra]

hi

try this one & i dont think that this requires any loop

[VBA]Sub findLiquid_selection()
Selection.Replace What:="SL det(conc)-MaxKleen 9", Replacement:="MaxKleen 9", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

Selection.Replace What:="SL det(liq)-Dynamo", Replacement:="Dynamo", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
[/VBA]

the above code work only on your selected data area if u want to run on activesheet use this


[VBA]Sub findLiquid_activesheet()
cells.Replace What:="SL det(conc)-MaxKleen 9", Replacement:="MaxKleen 9", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

cells.Replace What:="SL det(liq)-Dynamo", Replacement:="Dynamo", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
[/VBA]

the above code is tested in 2003

[Charlize]

What about the word Total in a cell ? A little guess that this will be changed to. First check on total in cell and replace that with everything after the -.[VBA]Dim vloop As Long
Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
For vloop = lrow To 7 Step -1
If Right(Range("A" & lrow), 5) = "Total" Then
Range("A" & lrow).Value = Split(Range("A" & lrow), "-")(1)
End If
lrow = lrow - 1
Next vloop[/VBA]

[Charlize]

Maybe try this ? It's based on the partly cleaned worksheet[vba]Option Explicit
Option Compare Text
Public Const vbrose As Long = 38
Sub desired_result()
Dim vloop As Long, lrow As Long
Dim concboolean As Boolean, liqboolean As Boolean, powboolean As Boolean
concboolean = False
liqboolean = False
powboolean = False
lrow = Range("A" & Rows.Count).End(xlUp).Row
For vloop = lrow To 7 Step -1
If Right(Range("A" & vloop), 5) = "Total" And _
Range("A" & vloop) <> "Grand Total" Then
Range("A" & vloop).Value = Split(Range("A" & vloop), "-")(1)
End If
Next vloop
lrow = Range("A" & Rows.Count).End(xlUp).Row
For vloop = 7 To lrow
If Range("A" & vloop) <> vbNullString Then
Select Case Right(Split(Range("A" & vloop), "-")(0), 3)
Case "iq)"
If liqboolean = False Then
Range("A" & vloop) = Split(Range("A" & vloop), "-")(1)
Range("A" & vloop).Offset(-1, 0).Value = "LIQUID"
Range("A" & vloop).Offset(-1, 0).Resize(, 15).Interior.ColorIndex = vbrose
liqboolean = True
End If
Case "nc)"
If concboolean = False Then
Range("A" & vloop).Value = Split(Range("A" & vloop), "-")(1)
Range("A" & vloop).Offset(-1, 0).Value = "CONC"
Range("A" & vloop).Offset(-1, 0).Resize(, 15).Interior.ColorIndex = vbrose
concboolean = True
End If
Case "wd)"
If powboolean = False Then
Range("A" & vloop).Value = Split(Range("A" & vloop), "-")(1)
Range("A" & vloop).Offset(-1, 0).Value = "POWDER"
Range("A" & vloop).Offset(-1, 0).Resize(, 15).Interior.ColorIndex = vbrose
powboolean = True
End If
End Select
End If
Next vloop
lrow = Range("A" & Rows.Count).End(xlUp).Row
For vloop = lrow To 6 Step -1
If Range("A" & vloop) = "LIQUID" Or Range("A" & vloop) = "CONC" _
Or Range("A" & vloop) = "POWDER" Then
Range("A" & vloop).EntireRow.Insert
ElseIf Range("A" & vloop) <> vbNullString Then
Select Case Right(Split(Range("A" & vloop), "-")(0), 3)
Case "iq)"
Range("A" & vloop) = Split(Range("A" & vloop), "-")(1)
Case "nc)"
Range("A" & vloop).Value = Split(Range("A" & vloop), "-")(1)
Case "wd)"
Range("A" & vloop).Value = Split(Range("A" & vloop), "-")(1)
End Select
End If
Next vloop
End Sub[/vba]

[fwawergurl16]

hi

try this one & i dont think that this requires any loop

[vba]Sub findLiquid_selection()
Selection.Replace What:="SL det(conc)-MaxKleen 9", Replacement:="MaxKleen 9", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

Selection.Replace What:="SL det(liq)-Dynamo", Replacement:="Dynamo", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
[/vba]
the above code work only on your selected data area if u want to run on activesheet use this


[vba]Sub findLiquid_activesheet()
cells.Replace What:="SL det(conc)-MaxKleen 9", Replacement:="MaxKleen 9", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

cells.Replace What:="SL det(liq)-Dynamo", Replacement:="Dynamo", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
[/vba]
the above code is tested in 2003

Hi anandbohra. If there's no loop, then it'll only do once, isn't it? I need the code to run for the entire worksheet. Plus, it has to be a dynamic code cos it's not only for this particular worksheet, but other worksheets of the same format, but different product brands.

[mdmackillop]

FYI
If you need to loop a Find, check out FindNext Method in VBA Help

Example

This example finds all cells in the range A1:A500 that contain the value 2 and changes their values to 5.
[vba]
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
[/vba]

[fwawergurl16]

anandbohra, Charlize, & mdmackillop,

Thanks for all the help. Finally got it done. Thanks for the advice & codes!

[anandbohra]

ok

but dont forget to mark thread as solved
on the top under thread tools