Referring to a range on another worksheet from within the worksheet module

Hot Topic

Update document properties on MS site server
microsoft word 200 trouble
export lots of Access stored queries
WORKING ON WORKBOOK WITHOUT OPENING IT
Suppress Linked View Unique Record Popup
How Can I create an UDF I can use in a cell?
Insert Customer Reference Information
Problem with CWnd::RunModalLoop(DWORD dwFlags) in wincore.cpp
Bound form
How can I run sql command with no cofirmation alert through VBA

Microsoft ISV

How can filter object ?
Unable to create .NET class in VBA
How can I determine if a character is number?
Migrate Access Forms with VBA to VB.NET
Business Scorecard 2005 Installed - Just cannot connect to Analysis Services database
Authentication schemes in LCS ?
ISV Competency Partners left behind for Office 12 Beta
Excel DDE Server
Data Monitoring Application...
How to put text in a listbox heads ?

Referring to a range on another worksheet from within the worksheet module

I cannot seem to get the range with cells to work from a worksheet module for a range on another worksheet.

The following code gives me an "application-defined or object-defined error" on the If statement line.

With Worksheets("ConceptData")
If Range("NumTechLines") <> .Range(.Cells(iRowNumber, 10)) Then MsgBox .Range(.Cells(iRowNumber, 10))
End With

When I replace the cells method with an absolute address "J6", I do not get the error.  I have verified that the Range("NumTechLines") and iRowNumber  are defined.

Answer this question

Referring to a range on another worksheet from within the worksheet module

  • Ken Lyon

    This code gives me the same "object-defined" error.

  • m.singh

    Works fine for me.

    I would check where you've declared and set the value of iRowNumber; I set it locally in the procedure     in the Sheet2 code as follows:

    Sub go()
    Dim     iRowNumber As Integer
    iRowNumber = 1

    With Worksheets("ConceptData")
    If .Range("NumTechLines") <> .Cells(iRowNumber, 10) Then MsgBox .Cells(iRowNumber, 10)
    End With

    End Sub

  • Tomas Restrepo

    I have declared iRowNumber at the top of the sheet module and I have placed msgbox statements immediately prior to this code which shows that both .Range("NumTechLines") and iRowNumber are defined correctly entering this code.  I have definitely determined that the .Cells(iRowNumber, 10) or .Range(.Cells(iRowNumber, 10)) is the offending code.  Also bear in mind that I have placed this code in a sheet module other than Sheet1.  I think this may be the problem, but I cannot figure out how to work around it.

  • omarsaid

    Try -

    With Worksheets("ConceptData")
    If .Range("NumTechLines") <> .Cells(iRowNumber, 10) Then MsgBox .Cells(iRowNumber, 10)
    End With
  • Referring to a range on another worksheet from within the worksheet module
Answer this question