View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Susan Susan is offline
external usenet poster
 
Posts: 1,117
Default STUPID range object error

Public Sub Copy_2_Cohort()

Set wsCohort = wb.Worksheets("CohortList")
iCohortRow = wsCohort.Cells(5000, 2).End(xlUp).Offset(1, 0).Row
Set rCohortStudent = wsCohort.Range("b" & iCohortRow)
Set rClass = wsCohort.Range("a" & iCohortRow)

rCohortStudent = sStudent
rClass = sClass

End Sub

============================
all variables are dimmed as indicated in a global variables module.

there's no reason (that i can see) why this doesn't work! half of the
time it runs through, but either rCohortStudent or rClass is left
blank (when the intellisense & various test messageboxes give the
correct range.address & text). the other half of the time it breaks
on either rCohortStudent OR rClass being unidentified objects. when
it was rClass that was left blank, i tried

Set rClass = rCohortStudent.Offset(0,-1)

that worked once, but then started breaking an error - saying object
wasn't set (when intellisense showed that rCohortStudent WAS set,
properly!).

i tried setting these ranges as variants (vClass), but that didn't
work.

i've compared these statements to working statements in other subs,
and i don't see what the problem is!

this is part of a huge, ungainly macro, so i thought perhaps since it
broke out of code an application.enableevents (or something) hadn't
been reset to true, so i ran another sub:

Sub reset_everything()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.DataEntryMode = True
End Sub

didn't help.

at this point i am considering reworking the structure of the macros &
modules so they flow better (more in order rather than go-over-here,
go-over-there).

any ideas?
thanks a lot!
susan