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

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 ExplicitSub Copy_A_to_DF()Dim xLast_Row As LongWith 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 WithEnd Sub

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

RichardH1976Author Commented:

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

compile error argument not optional

0

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

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.

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 ExplicitSub Copy_A_to_DF()Dim i As LongDim j As LongDim xLast_Row As LongApplication.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseWith 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 NextEnd WithApplication.ScreenUpdating = TrueApplication.Calculation = xlCalculationManualEnd Sub

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.

Open in new window

Regards,Brian.