Need to know why this fails

[wut]

Whenever I try to use "Range" as a method in this block of code, I get an error saying range method of object class failed. Those exact same methods work just fine when they're placed into any other macro.

Any suggestions?

(The two red lines are causing the failure... the ones that specify a "Range")

sub test()
 
'==================================================
'Opens the browser and copies stuff
'==================================================
Dim Browser As Object
Set Browser = CreateObject("InternetExplorer.Application")
link = "http://www.google.com"
Browser.Navigate (link)
Browser.Visible = True
While Browser.Busy And Browser.ReadyState <> 4: DoEvents: Wend
Browser.ExecWB 17, 0
Browser.ExecWB 12, 0
Browser.Quit

'==================================================
'Pastes all website contents into worksheet
'Finds text, selects range to copy
'==================================================
 
ThisWorkbook.Sheets("Sheet1").Paste Destination:=Sheets("Sheet1").Range("A1")
For Each Cell In ActiveSheet.UsedRange
    If Cell.Value = "text" Then
        a = Cell.Offset(1, 0).Address
    End If
Next
ThisWorkbook.Sheets("Sheet1").Range(a, Range(a).SpecialCells(xlCellTypeLastCell)).Copy
ThisWorkbook.Sheets("Sheet1").Range("H1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 
end sub

 

[Dryver14]

are you trying to say

Range("A:A")

 

[leigao84]

Are you sure cell "a" gets defined when you run the macro?
Make a watch on "a" and do a debug run.

 

[wut]

Dryver: Any use of the Range method fails. I've tried it ten ways from Sunday.

leigao: Yeah, the cell I want to target using the variable "a" is getting targeted.

I just went back and tried the code I posted in the OP. It fails exactly the way I thought, so there's no mistake. Try commenting out the two red lines and you'll see exactly what I'm talking about.

This shouldn't be happening. What's going on...?

 

[wut]

This is totally ridiculous....

Just to make sure both halves of the code worked fine, I split the two halves into two macros. Not only do the two macros work independently, but the whole package works great when the first macro calls the second. The failure only occurs when the two halves are integrated into one procedure.

I'll post the tweaked code in a bit.

 

[leigao84]

maybe it fails because you're opening a new window? Have you tried to do ThisWorkbook.Activate?

 

[wut]

I figured exactly the same thing. I did exactly that. Doesn't seem to do any good.

 

[wut]

I think I might need to do a web query to get around this problem. I've never done that before, so I don't even know where to start.