search and replace data

[Anthony]

In sheet1 cell A2 I have a ref number eg 1234567
I would like a macro to search through cells A12:A100 of sheet2 for the same
ref number and when found replace the data in that row (columns A:O) with the
data held in cells A2:O2 of sheet1
any ideas please?
many thanks

 

[Mike H]

Anthony,

You don't say if there would be multiple instances on sheet 2 so this will
find them all and paste the data in. Right click sheet 2 sheet tab, view code
and paste this in.

Sub search_Replace()
Dim myrange As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & lastrow)
For Each c In myrange
If c.Value = Sheets("Sheet1").Range("A2").Value Then
Sheets("Sheet1").Range("A2").Resize(, 15).Copy
c.PasteSpecial
End If
Next
End Sub

If there will only be one instance then add the line
Exit Sub
After the pastespecial line

Mike

 

[Per Jessen]

Hi

Try this:

Sub Anthony()
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim Targetrange As Range

Set sh = Worksheets("Sheet1")
Set sh1 = Worksheets("Sheet2")

With sh1.Range("A12:A100")
Set c = .Find(what:=sh.Range("A2").Value, Lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Ref. # do not exists!")
Exit Sub
End If
End With

sh.Range("A2:O2").Copy Destination:=sh1.Cells(c.Row, 1)
End Sub

Regards,
Per

 

[marcus]

Hi Anthony

This will replace all found instances of sheet1 A2, in Sheet 2 in the
range given.

Sub FindRplce()

Set rngA = Sheets("Sheet1").Range("A2")
Set rngB = Sheets("Sheet2").Range("A12:A100")
Set CpyRng = Sheets("Sheet1").Range("A2:O2")

For Each CellB In rngB
Found = False
For Each cellA In rngA
If CellB.Value = cellA.Value Then
Found = True
CpyRng.Copy CellB
End If
Next
Next
End Sub

Regards

Marcus

 

[Anthony]

Many thanks to you all for ur efforts,
Just one 'problem'
when the data is pasted into the correct row in sheet2 the A cell some how
is populatated with a formula =SHEET1!A12

any reason why this is doing so as it returns the value 0 (zero)
thanks

 

[Rick Rothstein \(MVP - VB\)]

Does this do what you want?

Sub TransferData()
Const StartCol As String = "A"
Const EndCol As String = "O"
Const SourceSheet As String = "Sheet1"
Const SourceRow As Long = 2
Const DestSheet As String = "Sheet2"
Const DestStartRow As Long = 12
Dim DestEndRow As Long
Dim X As Long
Dim Y As Long
With Worksheets(DestSheet)
DestEndRow = .Cells(Rows.Count, StartCol).End(xlUp).Row
For X = DestStartRow To DestEndRow
If .Cells(X, StartCol).Value = Worksheets(SourceSheet). _
Cells(SourceRow, StartCol).Value Then
For Y = Asc(StartCol) - 64 To Asc(EndCol) - 64
Worksheets(SourceSheet).Cells(SourceRow, Y).Copy .Cells(X, Y)
.Cells(X, Y).Value = .Cells(X, Y).Value
Next
End If
Next
End With
End Sub

Rick

 

[Anthony]

Great stuff,
thanks for all your efforts

Does this do what you want?

Sub TransferData()
Const StartCol As String = "A"
Const EndCol As String = "O"
Const SourceSheet As String = "Sheet1"
Const SourceRow As Long = 2
Const DestSheet As String = "Sheet2"
Const DestStartRow As Long = 12
Dim DestEndRow As Long
Dim X As Long
Dim Y As Long
With Worksheets(DestSheet)
DestEndRow = .Cells(Rows.Count, StartCol).End(xlUp).Row
For X = DestStartRow To DestEndRow
If .Cells(X, StartCol).Value = Worksheets(SourceSheet). _
Cells(SourceRow, StartCol).Value Then
For Y = Asc(StartCol) - 64 To Asc(EndCol) - 64
Worksheets(SourceSheet).Cells(SourceRow, Y).Copy .Cells(X, Y)
.Cells(X, Y).Value = .Cells(X, Y).Value
Next
End If
Next
End With
End Sub

Rick

 

[Anthony]

Mike,Per and Marcus - many thanks to you all for your efforts, I have managed
to get this to work so again thanks