I have two macros which work fine separately. I have combined them (see the line ' insert 12 columns and label) - now get an error.
Object variable or With block variable not set
I have no idea what this means. The macro executes all the way through to this section, then the error pops up:
Find the phrase "This Year"
Cells.Find(What:="This Year", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
' Capture column where this resides
myColumn = ActiveCell.Column
Here's the full code:
Sub border_highlight_insert()
‘ Change heavy border to new column, change tint – keyed to year A6
With ActiveSheet
Dim i As Integer
i = [A6] - 2010
.Range(.Cells(9, i), .Cells(50, 4)).Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
.Range(.Cells(9, i), .Cells(50, 4)).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("E9:E50").Select
Range("E50").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434828
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
‘ insert 12 columns and label
Sheets(“Expense Tracking”).Select
Range(A1).select
Dim myColumn As Long
Dim myCol As Long
' Find the phrase "This Year"
Cells.Find(What:="This Year", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
' Capture column where this resides
myColumn = ActiveCell.Column
' Insert 12 blank columns to the left of this column
Range(Cells(8, myColumn), Cells(8, myColumn + 11)).EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
' Populate row 8 with month numbers
For myCol = 1 To 12
Cells(8, myColumn + myCol - 1) = Format(DateSerial(2000, myCol, 1), "mmm")
Next myCol
End With
End Sub
Any help would be much appreciated!!
Thanks,
Mike
Bookmarks