I am trying to go through a list of cells and add buttons to adjacent cells if they meet certain criteria. As a practice to get down the basic algorithm i wrote this code.
Option Explicit
Sub Create_Command_Buttons()
Dim currentCell As Range
Dim buttonArray(114) As OLEObject
Dim i As Integer
i = 1
currentCell = Range("J1")
Do While i <= 114
If currentCell.Offset(0, -2) = "" Then
Set buttonArray(i) = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=currentCell.Left, Top:=currentCell.Top, Height:=30, Width:=120)
End If
i = i + 1
currentCell = currentCell.Offset(1, 0)
Loop
End Sub
When I run it I get an error: Object variable or With block not set
Any information on how I could get this to compile would be greatly appreciated.
Option Explicit
Sub Create_Command_Buttons()
Dim currentCell As Range
Dim buttonArray(114) As OLEObject
Dim i As Integer
i = 1
currentCell = Range("J1")
Do While i <= 114
If currentCell.Offset(0, -2) = "" Then
Set buttonArray(i) = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=currentCell.Left, Top:=currentCell.Top, Height:=30, Width:=120)
End If
i = i + 1
currentCell = currentCell.Offset(1, 0)
Loop
End Sub
When I run it I get an error: Object variable or With block not set
Any information on how I could get this to compile would be greatly appreciated.