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

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 & 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?

This question is related to excel excel-2007 vba

The answer is


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.


Change

Range(DataImportColumn & 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

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" & DataImportRow & "C" & DataImportColumn).Offset(0, 2).Value = iFirstCustomerSales

This should fix your problem.


Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to excel-2007

Declare a variable as Decimal filter out multiple criteria using excel vba Excel Formula which places date/time in cell when data is entered in another cell in the same row Creating a list/array in excel using VBA to get a list of unique names in a column Delete all data rows from an Excel table (apart from the first) Excel formula to get week number in month (having Monday) Run-time error '1004' - Method 'Range' of object'_Global' failed A formula to copy the values from a formula to another column Excel how to find values in 1 column exist in the range of values in another Delete entire row if cell contains the string X

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?