Solved

VBA:  FIND AND REPLACE

Posted on 2013-02-06
8
440 Views
Last Modified: 2013-02-06
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
0
Comment
Question by:RichardH1976
  • 4
  • 4
8 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38861164
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
 

Author Comment

by:RichardH1976
ID: 38861236
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
 

Author Comment

by:RichardH1976
ID: 38861331
by the way my error is on the range.. and I get

compile error argument not optional
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:redmondb
ID: 38861372
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
 

Author Comment

by:RichardH1976
ID: 38861422
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
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38861479
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
 

Author Closing Comment

by:RichardH1976
ID: 38861743
Great Solution...
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38861903
Thanks, Richard.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question