Find and Replace a cell value of unknown location

[mellowe]

Is there a function I can use to find a text string in a particular
column -
say in column (H) for string 'Mytotal' to be replaced by 'Subtotal'
when the
cell location of 'Mytotal' is unknown only the column is known? And
also is
there a function that can find a certain value in a column then delete
the
row below the found value? e.g in column (H) find 'Subtotal' then
delete the
row below the occurance of 'Subtotal'. Help is much appreciated with
this one?

 

[Don Guillett]

To use a function you can use MATCH to find the value in a column. look in
the help index. However, to replace you would need to use a FIND macro. Look
in the VBA help indes for FIND.

 

[mellowe]

thanks for that but doesn't the MATCH function just return the position
of the item? I really need the value to be found then replaced with
another value automatically..can I use a macro to do this?

 

[Dave Peterson]

Maybe something like this will give you an idea:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim FoundCell As Range
Dim WhatToFind As String
Dim WhatToReplace As String

WhatToFind = "myTotal"
WhatToReplace = "Subtotal"

With ActiveSheet
Set myRng = .Range("H:H")
With myRng
Set FoundCell = .Cells.Find(what:=WhatToFind, _
after:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox WhatToFind & " wasn't found"
Exit Sub
End If

FoundCell.Replace what:=WhatToFind, _
replacement:=WhatToReplace, lookat:=xlWhole, _
searchorder:=xlByRows, MatchCase:=False

FoundCell.Offset(1, 0).EntireRow.Delete

End With
End Sub

 

[mellowe]

Brilliant Dave!! Thanks!
Works great although is there a loop I can use here to find every
occurrence of 'myTotal', replace it with 'SubTotal' and delete the row
below it ?

 

[Dave Peterson]

One way:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim FoundCell As Range
Dim WhatToFind As String
Dim WhatToReplace As String

WhatToFind = "myTotal"
WhatToReplace = "Subtotal"

With ActiveSheet
Set myRng = .Range("H:H")
With myRng
Do
Set FoundCell = .Cells.Find(what:=WhatToFind, _
after:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
Exit Sub 'done
End If

FoundCell.Replace what:=WhatToFind, _
replacement:=WhatToReplace, lookat:=xlWhole, _
searchorder:=xlByRows, MatchCase:=False

FoundCell.Offset(1, 0).EntireRow.Delete
Loop
End With
End With
End Sub

 

[mellowe]

Thanks again Dave I was able to use this in my macro ....saved me lots
of frustration!! Brilliant!