CODEWITHSUNDEEP
vbaexcel
mathgenius
mathgenius

Reputation: 513

VBA cannot correctly handle range returned by find method

I have this code:

Private Sub UserForm_Initialize()

Dim Text_Cell As Range
Dim temp As Control

Set Text_Cell = Sheet1.Cells.Find("Text")

Do Until Text_Cell.Offset(1, 0).Value = ""
    Set temp = UserForm1.MultiPage(1).Controls.Add("Forms.Commandbutton.1", "h")

    Set Text_Cell = text_Cell.Offset(1, 0)
Loop

EDIT: Just a quick clarification - using MsgBoxes, breakpoints, science, magic, etc. I found out the cause of the "Object required" error occurs well before VBA gets to the loop.

I am trying to find a cell in a sheet that has the value of "text" then for each cell with a value below it - create a control on my userform. The problem I am having is that the Find method finds the correct cell, returns and assigns it but for some reason VBA cannot handle the "text_Cell" variable, even though after setting a breakpoint and taking a look at the local variable of "text_Cell" I saw it is assigned to the proper cell. I keep getting "Object Required" error. Getting the cell alone returns nothing, hence trying to reference Offst(1,0) raises the "Object required" error.

Upvotes: 2

Views: 82

Answers (1)

user4039065
user4039065

Reputation:

I am trying to find a cell in a sheet that has the value of "text" then for each cell with a value below it - create a control on my userform.

Use the following to cycle through all of the non-blank cells beneath the cell with text.

Set Text_Cell = Sheet1.Cells.Find(What:="text", MatchCase:=False, _
                              LookAt:=xlWhole, LookIn:=xlFormulas)

Do Until Text_Cell.Value = ""
    Set temp = UserForm1.MultiPage(1).Controls.Add("Forms.Commandbutton.1", "h")

    Set text_Cell = text_Cell.Offset(1, 0)
Loop

Upvotes: 1

Related Questions