Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Beginning VB 6
|
Beginning VB 6 For coders who are new to Visual Basic, working in VB version 6 (not .NET).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 28th, 2009, 04:23 PM
Registered User
 
Join Date: Jan 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Run-time error '1004' with Named cells

Hi - I'm new to VBA programming and am trying to write a macro in Excel. I have a named cell (LR_FileName) where the user will input the name of the file. The macro will then activate that file (should already be open) and select and copy cells from that file to the file with the macro. However, when I run the code, I receive the following error:
Run-time error '1004':
Method 'Range' of object' _Global' failed

Here is the section of code that it is tripping up on:
Code:
Sub CDSS_Data_Fill_2_ColB()
    Windows(Range("CDSS_Name").Value).Activate
    Application.ScreenUpdating = False
    Range("G2").Select
    Selection.Copy
    Windows(Range("LR_FileName").Value).Activate
    Range("B6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
When I run the debug, the line Windows(Range("LR_FileName").Value).Activate is highlighted.

If I change the code to just activate a file name instead of a referenced Named cell, I get the same error but on the next line (Range("B6").Select

Thanks for any help!
 
Old January 29th, 2009, 01:04 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

I think you need to check for two things here

1. Presence of Named Range
2. Presence of the Workbook in the current Excel instance.

Here is a hint which you can use

Dim RngCDSS As Range
Dim oWB As Workbook

Set RngCDSS = Range("CDSS_Name")
If RngCDSS Is Nothing Then MsgBox "Name CDSS_Name Doesn't Exist": Exit Sub

For Each oWB In Workbooks
If InStr(1, oWB.name, RngCDSS.Value) <> 0 Then
Windows(oWB.name).Activate
Exit For
End If
Next

Set RngLR = Range("LR_FileName")
If RngLR Is Nothing Then MsgBox "Name LR_FileName Doesn't Exist": Exit Sub


For Each oWB In Workbooks
If InStr(1, oWB.name, RngLR .Value) <> 0 Then
Windows(oWB.name).Activate
Exit For
End If
Next

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Run time error 1004 Ken Matthews Excel VBA 1 March 2nd, 2007 06:30 AM
HELP! PLEASE referencing named cells sebdanger Excel VBA 6 January 13th, 2005 12:40 AM
Run-time error '1004' Document not saved Tucky Excel VBA 0 October 28th, 2004 12:04 AM
Run-time error '1004': raja.gogoi VB Components 1 July 18th, 2004 11:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.