Run-time error 1004 - Method Range of object Global failed

Question

I have a problem in VBA with a line throwing back an error    What the macro is intended to do is find a particular cell then paste data into it   The code is as following    To find Column of Customer imput For Each cell In Range  B4 M4            If cell Value   strLeftMonth Then             DataImportColumn   cell Column          End If  Next   For Each cell In Worksheets  data customer monthly 2013   Range  A3 A9999     First Customer If cell Value   strFirstCustomer Then         DataImportRow   cell Row      Range DataImportColumn  amp  DataImportRow  Offset 0  2  Value   iFirstCustomerSales      End If   After running the above code  The code crashes giving the 1004 run-time error on the asterisk d line  Also DataImportColumn has a value of 7 and DataImportRow has a value of 5    Now my concern is that Columns aren t referenced as numbers but letters  so it must be that my code can never work because its a terrible reference    Does anyone have any suggestions how I can make the above work

User · Answer

Change  Range DataImportColumn  amp  DataImportRow  Offset 0  2  Value   to  Cells DataImportRow DataImportColumn  Value   When you just have the row and the column then you can use the cells   object  The syntax is Cells Row Column   Also one more tip  You might want to fully qualify your Cells object  for example  ThisWorkbook Sheets  WhatEver   Cells DataImportRow DataImportColumn  Value

User · Answer

When you reference Range like that it s called an unqualified reference because you don t specifically say which sheet the range is on   Unqualified references are handled by the   Global  object that determines which object you re referring to and that depends on where your code is   If you re in a standard module  unqualified Range will refer to Activesheet   If you re in a sheet s class module  unqualified Range will refer to that sheet   inputTemplateContent is a variable that contains a reference to a range  probably a named range   If you look at the RefersTo property of that named range  it likely points to a sheet other than the Activesheet at the time the code executes   The best way to fix this is to avoid unqualified Range references by specifying the sheet   Like  With ThisWorkbook Worksheets  Template        Range inputTemplateHeader  Value   NO ENTRY      Range inputTemplateContent  Value   NO ENTRY End With   Adjust the workbook and worksheet references to fit your particular situation

User · Answer

Your range value is incorrect  You are referencing cell  75  which does not exist  You might want to use the R1C1 notation to use numeric columns easily without needing to convert to letters   http   www bettersolutions com excel EED883 YI416010881 htm  Range  R   amp  DataImportRow  amp   C   amp  DataImportColumn  Offset 0  2  Value   iFirstCustomerSales   This should fix your problem

[excel] Run-time error '1004' - Method 'Range' of object'_Global' failed

Examples related to excel

Examples related to excel-2007

Examples related to vba