Solved

VBA:  FIND AND REPLACE

Posted on 2013-02-06
8
431 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

867 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

22 Experts available now in Live!

Get 1:1 Help Now