VBA - Object Variable or With Block not set
 

To all -

I get the error in the subject line when I try to reset the variabl
"sAddress" at the end of the loop. I think it has something to do wit
the loop but I am not sure being this is my first Excel VBA Program.
Of Course this is only a portion of the entire code.

The data I search through is set up thusly

A Name

Various Dates

"Agent"

The data repeats itself with there being multiple instances of the sam
name. Between each date is a set of data. I am attempting to searc
through the sheet from top to bottom for each instance of the name an
get certain data from each instance. The data is always in this orde
and the string "Agent" is at the end of the data for each Name. I ge
the address of the current instance of Name and use it as the rang
between it and the end of the sheet "sSklstEnd" to search for the th
string "Agent". I use the address of agent to form a range between th
Name address and the "Agent" address to search for a given date. I
the date is not present, I extract other needed data and then I attemp
to find the next instance of the Name to go through the previou
process. I am attempting to terminate the loop when the given rang
does not yeild another instance of the Name.

If more info is needed pls let me know. Thanks.


Case Is 1
sAddress = Range("A1:"
sSklstEnd).Find(sNameOnRpt).Address
iLoopCnt = 1
Do
sAgentAddress = Range(sAddress & ":"
sSklstEnd).Find("Agent").Address
If Not Range(sAddress & ":"
sAgentAddress).Find(sDate) Is Nothing Then
Call sCall_Data(iRowCnt1, sNameOnRpt, sSklstEnd
sDNEnd, False, iFreq, iLoopCnt)
End If
Call sCall_Data(iRowCnt1, sNameOnRpt, sSklstEnd
sDNEnd, True, iFreq, iLoopCnt)
sAddress = Range(sAgentAddress & ":"
sSklstEnd).Find(sNameOnRpt).Address
iLoopCnt = iLoopCnt + 1
Loop While Not Range(sAgentAddress & ":"
sSklstEnd).Find(sNameOnRpt) Is Nothing
End Selec

--
Message posted from http://www.ExcelForum.com

VBA - Object Variable or With Block not set
 

Is it possible that Range(sAgentAddress & ":" & sSklstEnd).Find(sNameOnRpt)
returns nothing? That would certainly explain an ObjectVariable error when
you try to get Nothing's Address.

George Nicholson

Remove 'Junk' from return address.


"Redwings " wrote in message
...
To all -

I get the error in the subject line when I try to reset the variable
"sAddress" at the end of the loop. I think it has something to do with
the loop but I am not sure being this is my first Excel VBA Program.
Of Course this is only a portion of the entire code.

The data I search through is set up thusly

A Name

Various Dates

"Agent"

The data repeats itself with there being multiple instances of the same
name. Between each date is a set of data. I am attempting to search
through the sheet from top to bottom for each instance of the name and
get certain data from each instance. The data is always in this order
and the string "Agent" is at the end of the data for each Name. I get
the address of the current instance of Name and use it as the range
between it and the end of the sheet "sSklstEnd" to search for the the
string "Agent". I use the address of agent to form a range between the
Name address and the "Agent" address to search for a given date. If
the date is not present, I extract other needed data and then I attempt
to find the next instance of the Name to go through the previous
process. I am attempting to terminate the loop when the given range
does not yeild another instance of the Name.

If more info is needed pls let me know. Thanks.


Case Is 1
sAddress = Range("A1:" &
sSklstEnd).Find(sNameOnRpt).Address
iLoopCnt = 1
Do
sAgentAddress = Range(sAddress & ":" &
sSklstEnd).Find("Agent").Address
If Not Range(sAddress & ":" &
sAgentAddress).Find(sDate) Is Nothing Then
Call sCall_Data(iRowCnt1, sNameOnRpt, sSklstEnd,
sDNEnd, False, iFreq, iLoopCnt)
End If
Call sCall_Data(iRowCnt1, sNameOnRpt, sSklstEnd,
sDNEnd, True, iFreq, iLoopCnt)
sAddress = Range(sAgentAddress & ":" &
sSklstEnd).Find(sNameOnRpt).Address
iLoopCnt = iLoopCnt + 1
Loop While Not Range(sAgentAddress & ":" &
sSklstEnd).Find(sNameOnRpt) Is Nothing
End Select


---
Message posted from http://www.ExcelForum.com/

VBA - Object Variable or With Block not set
 

I believe your simply missing the Set command try

Set sAddress = Range (.....)


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

VBA - Object Variable or With Block not set
 

Except that "sAddress = Range (yada)" doesn't appear in his code that I can
see. However, "sAddress = Range (yada).ADDRESS" does appear, and that
results in a string, not an object, so using Set isn't necessary.
--
George Nicholson

Remove 'Junk' from return address.


"Edward Ulle" wrote in message
...
I believe your simply missing the Set command try

Set sAddress = Range (.....)


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!