• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

cell function not defined

On trying to return the address of a cell located using a vlookup and match formula I am getting the compile error saying that sub or function has not been defined on the below line of code.  The vlooukup and match work indepandantly - it is just with the introduction of the cell function that it falls over.  

Me.txtReturn = cell("Address", WorksheetFunction.VLookup(ddateformated, Worksheets("Data").Range("B6:T666"), WorksheetFunction.Match(Me.cboRep_list, Worksheets("Data").Range("B6:CZ6"), False), False))
0
susie2892
Asked:
susie2892
  • 4
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

cell() is not a valid VBA function. CELL() is a worksheet function only.

cheers, teylyn
0
 
susie2892Author Commented:
Thank Teylen.  Can you suggest an alternative function that I caould use instead of cell()?
0
 
Saqib Husain, SyedEngineerCommented:
What does the vlookup return?

The cells function can be used to get the address

Me.txtReturn = cells(row,column).address

You will have to provide the row number and the column number.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Index is an alternative. It returns a range, hence it can be used with the .Address property

If I have all my brackets and stuff correct, this should give you the cell address:

txtReturn = WorksheetFunction.Index(Range("B6:T666"), _
    WorksheetFunction.Match(ddateformated, Range("B6:B666"), 0), _
    WorksheetFunction.Match(Me.cboRep_list, Range("B6:CZ6"), 0)).Address

Open in new window


cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
ssaqibh has a point. My suggstion could be simplified to

txtReturn = cells( _
    WorksheetFunction.Match(ddateformated, Range("B6:B666") + 5, 0), _
    WorksheetFunction.Match(Me.cboRep_list, Range("B6:CZ6") + 5, 0)).Address

Open in new window


(not tested)
0
 
susie2892Author Commented:
OK - I realised my error here ssaqibh - I see where you were going with your question..  my lookup was looking up the value not the row number.  I have changed this to a match formula like the 2nd one and used the above code to return the location.  Thank you..
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Well, glad you found a solution without consulting my suggestions.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now