Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Object variable or With block variable not set

Status
Not open for further replies.

JohnEOasis

Programmer
Sep 3, 2008
32
US
When I run the following code I get an Object variable or With block variable not set error and can not figure out why any helpl would be appriciated.
Code:
    Dim InputRange As Range
    Dim OutputRange As Range
    
    Set InputRange = Sheets("Misc").Range("A18:E18")
    Set OutputRange = Sheets("Rec").Range("Q9:Y9")
     Do Until InputRange.Item(1, 1).Value = ""
        If InputRange.Item(1, 1).Value = Range("A1").Value Then
            OutputRange.Item(1, 1).Value = InputRange.Item(1, 2).Value
            OutputRange.Item(1, 3).Value = InputRange.Item(1, 4).Value
            OutputRange.Item(1, 5).Value = InputRange.Item(1, 5).Value
            Set InputRange = InputRange.Offset(1, 0)
            Set OutputRange = OutputRange.Offset(1, 0)
        Else
        Set InputRange = InputRange.Offset(1, 0)
        End If
    Loop
 
it fails on the first output line
Code:
 OutputRange.Item(1, 1).Value = InputRange.Item(1, 2).Value
 

Code:
OutputRange.Cells(1, 1).Value = InputRange.Cells(1, 2).Value

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Thank you for your post.

I tried it and I still am getting the same error.
 
Works fine for me as is

Is there other code that follows the snippet you've posted?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
I cannot believe I missed this... It was on the worksheet change event; the code changes the event = endless loop! Thank you for all your help this was just a boneheaded mistake and I did not even think it all the way through.
 
no probs - must admit I thought the same as Skip at 1st - never used .Items to refer to subsets of a range - always .Cells

Think I'll just stick to cells meself though!

I assume you know the fix..l.

Application.enableevents = false

code

application.enableevents = true

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top