Macro to locate a reference number in a table and edit the full row

[skf786]

Hello,

i have a table with headers in c31:h31, reference number being in column C. i am using a macro to add data to this table.

i am looking to add a 'Modify data' macro button to the sheet which picks up the reference number given in cell c25 (outside the table), then locates this reference number in column C of the table then replaces the full row with the contents of cells c25:h25.

Many thanks in advance,

SKF

 

[Zot]

Try this. Change Table and sheet names if necessary

Sub FindReplace()

Dim LookValue  As String
Dim rngSearch As Range, rngFound As Range
Dim ws As Worksheet
Dim Tbl As ListObject

Set ws = ActiveWorkbook.Sheets("Sheet1")
Set Tbl = ws.ListObjects("Table1")

LookValue = ws.Range("C25")
Set rngFound = Tbl.DataBodyRange.Columns(1).Find(LookValue, LookAt:=xlWhole)

If Not rngFound Is Nothing Then
    ws.Range("C25", "H25").Copy rngFound
End If

End Sub

 

[mohadin]

If You have no table
just try

Sub test()
Cells(Range("C25") + 30, 3).Resize(, 6) = Range("c25:h25").Value
End Sub

 

[skf786]

Try this. Change Table and sheet names if necessary

Sub FindReplace()

Dim LookValue  As String
Dim rngSearch As Range, rngFound As Range
Dim ws As Worksheet
Dim Tbl As ListObject

Set ws = ActiveWorkbook.Sheets("Sheet1")
Set Tbl = ws.ListObjects("Table1")

LookValue = ws.Range("C25")
Set rngFound = Tbl.DataBodyRange.Columns(1).Find(LookValue, LookAt:=xlWhole)

If Not rngFound Is Nothing Then
    ws.Range("C25", "H25").Copy rngFound
End If

End Sub

thanks Zot, this works perfectly

 

[Zot]

thanks Zot, this works perfectly

Glad could help

 

[skf786]

If You have no table
just try

Sub test()
Cells(Range("C25") + 30, 3).Resize(, 6) = Range("c25:h25").Value
End Sub

 

[skf786]

Thank you. i will try it on data other than a table.

 

[skf786]

Dear Zot,

im trying to use the attached macro on a protected sheet. im getting an error. can you please help me fix it. Also is it possible to paste 'special' into the table as 'Values'

thanks again for your help.

Sub FindReplace()

With ActiveSheet
      .Unprotect "7860"



Dim LookValue  As String
Dim rngSearch As Range, rngFound As Range
Dim ws As Worksheet
Dim Tbl As ListObject

Set ws = ActiveWorkbook.Sheets("1")
Set Tbl = ws.ListObjects("Table134568919")

LookValue = ws.Range("d22")
Set rngFound = Tbl.DataBodyRange.Columns(1).Find(LookValue, LookAt:=xlWhole)

If Not rngFound Is Nothing Then
    ws.Range("d22", "k22").Copy rngFound
End If


 .Protect "7860"


End Sub

 

[Zot]

I think it was just because you forget the With End statement

I re-arrange the code and change the ActiveSheet to ws since it has been declared to save typing ?. Now using Paste Special for value only. Somehow using PasteSpecial you cannot make it in single line because it will give error. Nee to move to next line. Not sure the reason because I'm not expert. ?

Sub FindReplace()

Dim LookValue  As String
Dim rngSearch As Range, rngFound As Range
Dim ws As Worksheet
Dim Tbl As ListObject

Set ws = ActiveWorkbook.Sheets("1")
Set Tbl = ws.ListObjects("Table134568919")

With ws
    .Unprotect "7860"
    LookValue = .Range("d22")
    Set rngFound = Tbl.DataBodyRange.Columns(1).Find(LookValue, LookAt:=xlWhole)
    If Not rngFound Is Nothing Then
        .Range("d22", "k22").Copy
        rngFound.PasteSpecial (xlPasteValues)
    End If
    .Protect "7860"
 End With

End Sub