Macro to find, replace.

[etradeguru]

I extract a list into a .txt file and then open in excel.The list
contents are from a dynamic source, but contain certain text items
that are constant. I want to be able to prompt for the word and then
find the next instance of that word in Row D, then navigate three
cells left and one cell down and then paste a formula. I need to loop
through this until the end of instances of the given word in column D.

Currently I have got as far as follows: -

Range("A1").Select
Cells.Find(What:="directory", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(1, -3).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-1]C[3],"" "",R[-1]C[4],"" "",R[-1]C[5],""
"",R[-1]C[6],"" "",R[-1]C[7],"" "",R[-1]C[8],"" "",R[-1]C[9])"
ActiveCell.Select
Selection.Copy
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(1, -3).Range("A1").Select
ActiveSheet.Paste

Now I need to loop through the rest of the document.
Help gratefully appreciated.
Mark

 

[Dave Peterson]

VBA's help shows a way to find the first match, then keep looking until you find
that first match.

Another option would be to count how many were in the range and just count until
you've done all of them.

That's what I used in this sample:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim StrToFind As String
Dim FoundCell As Range
Dim HowMany As Long
Dim iCtr As Long

Set wks = Worksheets("sheet1")

StrToFind = InputBox(Prompt:="Enter your string")
If Trim(StrToFind) = "" Then
Exit Sub
End If
With wks

With .Range("d1").EntireColumn
HowMany = Application.CountIf(.Cells, StrToFind)
If HowMany = 0 Then
MsgBox StrToFind & " was not found in column D"
Exit Sub
End If

Set FoundCell = .Cells.Find(what:=StrToFind, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
iCtr = 0
Do
iCtr = iCtr + 1
FoundCell.Offset(1, -3).FormulaR1C1 _
= "=R[-1]C[3]&"" ""&R[-1]C[4]&"" ""&R[-1]C[5]" _
& "&"" ""&R[-1]C[6]&"" ""&R[-1]C[7]" _
& "&"" ""&R[-1]C[8]&"" ""&R[-1]C[9]"

If iCtr = HowMany Then
Exit Do 'got them all
End If

Set FoundCell = .FindNext(after:=FoundCell)
Loop

End With
End With
End Sub

I extract a list into a .txt file and then open in excel.The list
contents are from a dynamic source, but contain certain text items
that are constant. I want to be able to prompt for the word and then
find the next instance of that word in Row D, then navigate three
cells left and one cell down and then paste a formula. I need to loop
through this until the end of instances of the given word in column D.

Currently I have got as far as follows: -

Range("A1").Select
Cells.Find(What:="directory", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(1, -3).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-1]C[3],"" "",R[-1]C[4],"" "",R[-1]C[5],""
"",R[-1]C[6],"" "",R[-1]C[7],"" "",R[-1]C[8],"" "",R[-1]C[9])"
ActiveCell.Select
Selection.Copy
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(1, -3).Range("A1").Select
ActiveSheet.Paste

Now I need to loop through the rest of the document.
Help gratefully appreciated.
Mark

 

[etradeguru]

Thanks for the help.
I also found a sneaky way round the problem.
I used a unique text string to identify each range position by using
the "find next/replace", using the replace to change the unique
string to be found, so that after all the instances had been found
and changed the error handler would kick in.
Hope that makes sense.
Mark