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
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.
Source: Stackoverflow.com