Find & Replace based on a Cell Value

[VanceLiving]

Hi guys,

I don't work in VBA too often and have been searching for hours for, what I thought would be, a simple Find and Replace code. All I'm trying to do is search for a cell's value (located in "B8") down a column ("B14":B"). If a match is located, I was hoping to replace that matched row with row values ("B8:Z8"). If no match is found, I am hoping to simply move along.

Any ideas would be very appreciated. Thanks so much!!!

 

[Fluff]

How about

Sub VanceLiving()
   Dim Fnd As Range
   
   Set Fnd = Range("B14", Range("B" & Rows.Count).End(xlUp)).Find(Range("B8").Value, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Fnd.Resize(, 25).Value = Range("B8:Z8").Value
End Sub

 

[VanceLiving]

Fluff - God bless you! Just for aesthetics, is there a way to incorporate the original formatting of "B8:Z8" into the code (number formats, etc.)?

Thanks again, very much appreciated!!

 

[Fluff]

How about

Sub VanceLiving()
   Dim Fnd As Range
   
   Set Fnd = Range("B14", Range("B" & Rows.Count).End(xlUp)).Find(Range("B8").Value, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Range("B8:Z8").Copy Fnd
End Sub

 

[VanceLiving]

That is awesome - thanks again!!

 

[Fluff]

You're welcome & thanks for the feedback