Record Macros - Copy & Paste Into Find Function

[LaurindaS]

Hi,

I’m fairly newto generating macros. I’ve been using record macros, but it’s not workingexactly the way I would like it to.

1. On tab “KIT”, select cell “O2”
2. Copy (In the example the text in O2, is “test”)
3. Go to tab “PLAN”, select column “B:B”
4. Find & paste
5. Close Find Pop-Up
6. Scroll to the left

This is what it’s recording. I’m not sure how to changethe Find “test” to Find whatever text is in cell O2.

Sheets("KIT").Select
Range("O2").Select
Selection.Copy
Sheets("PLAN").Select
Columns("B:B").Select
Selection.Find(What:="test", After:=ActiveCell,LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 11).Range("A1").Select

Any help with this would be greatly appreciated!

Thanks in advance!
Laurinda

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub LaurindaS()
   Dim Fnd As Range
   Sheets("Plan").Activate
   Set Fnd = Range("B:B").Find(Sheets("Kit").Range("O2").Value, , , xlPart, , , False, , False)
   If Not Fnd Is Nothing Then Fnd.Offset(, 11).Activate
End Sub

 

[LaurindaS]

Works perfectly!!! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback

 

[LaurindaS]

I'm wondering if you might be able to help me out once more? I found a potential for error in my macros. What I'm doing is pasting a section beside what is found from cell O2. The issue I found is that if the text that is in O2 is not in column B:B, it pastes that section in row 1 which is my header. Is there a way I can have it paste if it is found, or give an error message if it is not found?

Dim Fnd As Range
Sheets("PLAN").Activate
Set Fnd = Range("B:B").Find(Sheets("Kit").Range("O2").Value, , , xlPart, , , False, , False)
If Not Fnd Is Nothing Then Fnd.Offset(, 11).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Thanks again,
Laurinda

 

[Fluff]

How about

Sub LaurindaS()
   Dim Fnd As Range
   
   Sheets("Plan").Activate
   Set Fnd = Range("B:B").Find(Sheets("Kit").Range("O2").Value, , , xlPart, , , False, , False)
   If Not Fnd Is Nothing Then Fnd.Offset(, 11).Value = Sheets("Kit").Range("O2").Value
End Sub

 

[LaurindaS]

That is almost what I'm looking for, but what I would likecopied & pasted in that section is a section of a row. I've tried thismacros and it seems to only paste the 1st cell in the section.





Dim Fnd As Range


Sheets("PLAN DATA").Activate


Set Fnd =Range("B:B").Find(Sheets("KIT").Range("O2").Value,, , xlPart, , , False, , False)


If Not Fnd Is Nothing Then Fnd.Offset(, 11).Value =Sheets("SORT").Range("A1:KI1").Value





Any ideas?



Thanks,


Laurinda

 

[Fluff]

try

   If Not Fnd Is Nothing Then Fnd.Offset(, 11).Resize(, 295).Value = Sheets("Sort").Range("A1:KI1").Value

 

[LaurindaS]

This works perfect! Thank you so much for all your help! Really appreciate it!

 

[Fluff]

Glad to help & thanks for the feedback