Solved

VBA:  FIND AND REPLACE

Posted on 2013-02-06
8
430 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now