search and replace data
 

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

search and replace data
 

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

"Anthony" wrote:

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

search and replace data
 

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

"Anthony" skrev i meddelelsen
...
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

search and replace data
 

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

search and replace data
 

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

"marcus" wrote:

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

search and replace data
 

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" wrote in message
...
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

"marcus" wrote:

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

search and replace data
 

Great stuff,
thanks for all your efforts

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

"marcus" wrote:

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

search and replace data
 

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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

"marcus" wrote:

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