Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search and Replace, or Add New Row | Excel Discussion (Misc queries) | |||
Macro - Search one book, replace with data from another | Excel Programming | |||
How can I perform multiple search & replace - all data in spreadsheet? | Excel Programming | |||
Problem with search and replace data,thanks for you help in advance. | Excel Discussion (Misc queries) | |||
Excel VBA - Search And Replace Cell data | Excel Programming |