Worksheet Names (Object Required Error Msg)

Question

In the following code I get an 'Object Required' on the second likde of the subroutine.  What is missing?  The Sheets(1).Name function does work and returns the name of the first worksheet.

Thanks,

---

Dim Row As Integer

Dim TXFreq1High As Single

Sub Check_Data()

   TXFreq1High = Sheets(1).Name.Cells(Row, P) + Sheets(1).Name.Cells(Row, N) / 2

   TXFreq2Low = Sheets(1).Name.Cells(Row + 1, P) - Sheets(1).Name.Cells(Row + 1, N) / 2

End Sub

Answer 1

Sheets(1).Name returns a string variable with the name of the worksheet.  Then, since a string variable does not have a .Cells(...) property, VBA returns the error you encountered.

If you are interested in learning how to interpret code with references to multiple properties/methods, see

Case Study – Understanding code

http://www.tushar-mehta.com/excel/vba/vba-Understand%20code.htm

In the following code I get an 'Object Required' on the second likde of the subroutine.  What is missing?  The Sheets(1).Name function does work and returns the name of the first worksheet.

 

Thanks,

---

Dim Row As Integer

Dim TXFreq1High As Single

 

Sub Check_Data()

 

   TXFreq1High = Sheets(1).Name.Cells(Row, P) + Sheets(1).Name.Cells(Row, N) / 2

   

   TXFreq2Low = Sheets(1).Name.Cells(Row + 1, P) - Sheets(1).Name.Cells(Row + 1, N) / 2

   

 

End Sub

---

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present

Answer 2

Try one of these

TXFreq1High = Sheets(1).Cells(Row, p) + Sheets(1).Cells(Row, n) / 2

TXFreq2Low = Sheets(1).Cells(Row + 1, p) - Sheets(1).Cells(Row + 1, n) / 2

With Sheets(1)

   TXFreq1High = .Cells(Row, p) + .Cells(Row, n) / 2

   TXFreq2Low = .Cells(Row + 1, p) - .Cells(Row + 1, n) / 2

End With

Answer 3

Hi,

Try it this way assuming you have values for those variables it should work

TXFreq1High = Sheets(1).Cells(Row, p) + Sheets(1).Cells(Row, n) / 2

TXFreq2Low = Sheets(1).Cells(Row + 1, p) - Sheets(1).Cells(Row + 1, n) / 2

EDIT.. That was the problem, sheets(1) is an object and sheets(1).name is a property of the object

The Sheets(1).Name function does work and returns the name of the first worksheet.

---

If this post answers your question, please mark it as the Answer.

Mike H