VBA: FIND AND REPLACE

I would like to do a find and replace on my column A and replace rows D to F with the the value in column A.   Rows D to F have a formula which references another excel sheet and I need to update the formula with the reference from Column A.

Here is the  code I have so far..

Sub COPYANDPASTE()
Dim Replacement As String
Dim RowNum As Integer
RowNum = 2
Range("A2").Select
Do
    Replacement = ActiveCell.Value
    Range("D" & RowNum & ":F" & RowNum).Select
    Selection.Replace What:=Range, Replacement:=Replacement, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    RowNum = RowNum + 1
    Range("A" & RowNum).Select
Loop Until ActiveCell.Value = ""
End Sub

Thanks, Richard
RichardH1976Asked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
Richard,

Please see the code below. It might be a good idea to open the external files before running the macro,  otherwise Excel may take a long time to extract the data cell by cell from closed files.
Option Explicit

Sub Copy_A_to_DF()
Dim i         As Long
Dim j         As Long
Dim xLast_Row As Long


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Sheet1")

    xLast_Row = .UsedRange.Cells(1, 1).Row + .UsedRange.Rows.Count - 1
    If xLast_Row < 2 Then
        MsgBox ("No data found in """ & .Name & """ - run cancelled.")
        Exit Sub
    End If
    
    For i = 2 To xLast_Row
        If .Range("A" & i) <> "" Then
            For j = 3 To 5
                .Range("A" & i).Offset(0, j).Formula = Replace(.Range("A" & i).Offset(0, j).Formula, "[00275.xlsx]", "[" & Range("A" & i) & ".xlsx]")
            Next
        End If
    Next

End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual

End Sub

Open in new window

Regards,
Brian.
0
 
redmondbCommented:
Hi, Richard.

I'm not sure whether you're trying to Find anything specific, but the following code will copy the values in column A (skipping row 1) to columns D:F.
Option Explicit

Sub Copy_A_to_DF()
Dim xLast_Row As Long

With ThisWorkbook.Sheets("Sheet1")

    xLast_Row = .UsedRange.Cells(1, 1).Row + .UsedRange.Rows.Count - 1
    If xLast_Row < 2 Then
        MsgBox ("No data found in """ & .Name & """ - run cancelled.")
        Exit Sub
    End If
    
    If .FilterMode Then .ShowAllData
    .Range("A2:A" & xLast_Row).Copy Destination:=.Range("D2:F2")

End With

End Sub

Open in new window

Regards,
Brian.
0
 
RichardH1976Author Commented:
hi Brian,
I have the following formula in columns d to f and have 2000 rows to update the 00275 with the relevant account number in column A.
='C:\Documents and Settings\USER1\My Documents\CM\[00275.xlsx]Sheet1'!$D$14
This links to the excel sheet 00275.xlsx which pulls my data for one row.. I have 2000 spreadsheets which are named  002757.xlsx   etc etc,   I could manaually update the 2000 rows but I am sure there must be a quicker way....

I will have a look and see if i can use some of your code.

Thanks. Richard
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
RichardH1976Author Commented:
by the way my error is on the range.. and I get

compile error argument not optional
0
 
redmondbCommented:
Thanks, Richard.

OK, now we're getting somewhere! If I've understood you correctly, you want to take the value in A1 and use it to replace the "00275" in the formulas in in columns D to F.

Is that correct? If so, if there's a value in a cell in column A, will there always be formulas in the D to F columns? And, will every formula have the string "00275"?

Finally, If the above is correct then my macro is a million miles from what you want - don't even think about using it!

Regards,
Brian.
0
 
RichardH1976Author Commented:
Hi Brian,
Yes you have got it.  You reponse explains excatly what I am trying to do.

 I have 00275 in every formula in the 2000 rows.  There is a formula in every row from D to F.   The codes does loop through every column but does not seem to like the range.

Richard.
0
 
RichardH1976Author Commented:
Great Solution...
0
 
redmondbCommented:
Thanks, Richard.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.