CODEWITHSUNDEEP
excelvbaselectoffset
B-Rye
B-Rye

Reputation: 72

Unable to select range using find and offset functions

My goal is to copy rows from Sheet("VBA") to a specific location in Sheet("COLUMBIA-TAKEDOWN"). The location is Offset(1,1) of the cell containing "P R O S P E C T S". The first part of my code works well enough however my problems begin with selecting and editing a row [Prospect.Offset(13,-1).Select]. It appears to be ignoring this line of code because the formatting lines that follow are not happening. It's not throwing out an error message.

I understand that I'm incorrectly selecting the row and therefore unable to make the formatting changes but I don't know how to correct this problem.

Application.ScreenUpdating = False

Dim Prospect As Range
Set Prospect = Sheets("COLUMBIA-TAKEDOWN").Cells.Find(what:="P R O S P E C T S")

Sheets("VBA").Visible = True
Sheets("VBA").Rows("13:25").Copy
Prospect.Offset(1, -1).Insert shift:=xlDown
Prospect.Offset(13, -1).Select
With Selection.Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Prospects.Offset(1, -1).Select
Sheets("VBA").Visible = False

End Sub

Upvotes: 0

Views: 44

Answers (1)

FAB
FAB

Reputation: 2569

The problem is that you are trying to insert rows in a cell range... they are not the same size, hence the error.

Give this a try... might need some more thinkering, but i`ve just reused your code.

Sub test()

Application.ScreenUpdating = False

Dim wb As Workbook: Set wb = ThisWorkbook
Dim sht As Worksheet: Set sht = wb.Sheets("Sheet1")
Dim ProspectRow As Long: ProspectRow = sht.Cells.Find(what:="P R O S P E C T S").Row + 1

wb.Sheets("VBA").Rows("13:25").Copy
sht.Rows(ProspectRow).Insert Shift:=xlDown

With sht.Rows(ProspectRow + 13).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

Application.ScreenUpdating = True

End Sub

EDIT: revamped the code for critics...

EDIT2: added the formatting...

Upvotes: 2

Related Questions