Compile Error: Object Required VBA

Question

I'm new to VBA. I'm trying to write a script that cleans up some data from an experiment. I keep getting an error saying "Object Required" and it highlights pold. Does anyone have any idea why?

As for the script, I'm trying to go down a column of participant numbers and map out what range each participant is in. There are around 30 lines per participant, and I want to define that as values in an array.

Sub Cleanthismofoup() 
Dim pranges(1 To 50) As Long 
Dim pbegin As Range
Dim pend As Range
Dim pold As Integer
Dim pnew As Integer
Dim pcell As Range
Dim pcounter As Long
Dim i As Long

Set pcell = Range("A1:A1")
Set pbegin = Range("A2:A2")
Set pold = Range("B2:B2").Value
pcounter = 0

'for every item, store value in pnew
' move down one line. Check pnew = pold
' if it is, do again. else create new range

For i = 1 To rngl
pcell = pcell.Offset(-1, 0)
pnew = pcell.Cells.Value
If pnnew <> pold Then pcell = pend
If pcell = pend Then
counter = counter + 1
pranges(counter) = pbegin
counter = counter + 1
pranges(counter) = pend
pbegin = pcell.Offset(-1, 0)
Else: pold = pnew
End If

i = i + 1
Next

End Sub

You seem to have a mix of pnew and pnnew going on but pnnew isn't declared anywhere. Add Option Explicit to the top of the module if it's not there already — barrowc
– barrowc, Commented Jul 11, 2012 at 0:57

hardikudeshi · Accepted Answer · 2012-07-06 05:29:19Z

4

The error is because you are using a Set keyword which is used to assign reference to the object. Since the output on the RHS of Set pold = Range("B2:B2").Value is an Integer, vba gives you an error. To resolve it simply remove the Set keyword. However I also noticed that you are using rng1 in the for loop without initializing the rng1 variable, in which case your for loop will never execute. You might also want to rectify that.

answered Jul 6, 2012 at 5:29

hardikudeshi

1,4915 gold badges18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JimmyPena Over a year ago

The Set keyword is also missing from this line: pcell = pcell.Offset(-1, 0)

user1505743 Over a year ago

Thanks. I had been messing around with that part and didn't save the rng1 part.

user1505743 Over a year ago

Also, does the pcell=pcell.Offset(-1,0) seem alright? I'm trying to have the range value stored in pcell to move one down the column.

Collectives™ on Stack Overflow

Compile Error: Object Required VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related