Solved

VBA:  FIND AND REPLACE

Posted on 2013-02-06
8
436 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 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