Finding a Title Heading, Cut and Paste

[Guzzlr]

Set Fnd = Range("6:6").Find("%", , , xlWhole, , , False, , False)
If Not Fnd Is Nothing Then
    Intersect(Range("M7:M5000"), Fnd.EntireColumn).Cut
    Range("Q7").PasteSpecial Paste:=xlPasteValues
End If

I wish to find "%", then cut and paste special values, but as usual, It tells me a variable is not set, which I thought it was.
What did I do wrong?
Thanks for the help

 

[BarryL]

I wouldnt know that much of Intersect as i generally stay away from it. Can you try the below?

Sub test()
Dim fnd As Range
Set fnd = Range("6:6").Find("%", , , xlWhole, , , False, , False)
If Not fnd Is Nothing Then
    Range(Cells(7, fnd.Column), Cells(5000, fnd.Column)).Cut Range("Q7")
    Range("Q7:Q5000").Value = Range("Q7:Q5000").Value
End If
End Sub

 

[Fluff]

This line

Intersect(Range("M7:M5000"), Fnd.EntireColumn).Cut

should just be

Fnd.EntireColumn.Cut

 

[James006]

Hello,

You could test

Set Fnd = Range("6:6").Find(What:="%", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

Hope this will help

 

[BarryL]

This line

Intersect(Range("M7:M5000"), Fnd.EntireColumn).Cut

should just be

Fnd.EntireColumn.Cut

Think OP only wants from row 7 down though. Obviously better if OP has no data in rows 1-6 or data that can be overwritten.

 

[Fluff]

Good point Barry.
In which case

Sub chk()
Dim Fnd As Range
Set Fnd = Range("6:6").find("%", , , xlWhole, , , False, , False)
If Not Fnd Is Nothing Then
    Intersect(Range("7:5000"), Fnd.EntireColumn).Copy
    Range("Q7").PasteSpecial xlPasteValues
    Intersect(Range("7:5000"), Fnd.EntireColumn).ClearContents
End If
End Sub

 

[Guzzlr]

Think OP only wants from row 7 down though. Obviously better if OP has no data in rows 1-6 or data that can be overwritten.

Cut From "T7" down, then special values paste into "Q7", so My code above is wrong in the sense that it sholdld not be M7 but T7.
Thanks

 

[Fluff]

If you know that it's col T, why use Find?