Method 'Range' of object '_Global' failed

[aberry]

I've saw several posts referencing this error but so far I've been able to fix this.

First time through the code runs and creates the spreadsheet, 2nd time through it errors on "Range("B2:F2").Select".

The program needs to run continuously so I can ask the user to exit it each time to sort this problem.

The code below replicates what I can trying to do and errors on me the 2nd time the button is pressed.

Any thoughts?

Thanks,

Andrew

Private Sub Command1_Click()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.Workbooks.Add
Set oSheet = oWB.ActiveSheet

oSheet.Activate
oSheet.Cells(2, 2).Value = "Test"
'errors on line below
Range("B2:F2").Select
Selection.Merge
Selection.Font.Bold = True

Set oSheet = Nothing
oWB.Close
Set oWB = Nothing
oXL.Quit
Set oXL = Nothing

End Sub

 

[PHV]

you may try this:
oSheet.Range("B2:F2").Select
oXL.Selection.Merge
oXL.Selection.Font.Bold = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[aberry]

Thanks for your prompt response.

Perfect fix.

That's what I get for trusting the macro code!