.Range(Cells(n,m),Cells(x,y)).address produces error?

[pjmorris]

I'm sure this is simple, but the following code produces an error "Application Defined or object-defined error". Grateful for your suggestions to correct.

Function AvailCol(nAvail) As String
    Dim cCol As Integer 'column in which availability should be found
    Dim nRows As Integer 'number of rows to be included in range
    
    cCol = Worksheets("CoE Manager Data").Range("$C$2:$U$2").Find(Left(nAvail, 3)).Column
    nRows = WorksheetFunction.CountIf(Worksheets("CoE Manager Data").Range("$B$3:$B$4000"), "<>0")
    
    AvailCol = Worksheets("CoE Manager Data").Range(Cells(3, cCol), Cells(nRows, cCol)).Address
End Function

I had hoped to replace this rather ungainly formula with 'AvailCol':

   = OFFSET('CoE Manager Data'!$C$3,0,MATCH(LEFT('ECP Summary'!$B10,3),'CoE Manager Data'!$C$2:$U$2,0)-1,COUNTIF('CoE Manager Data'!$B$3:$B$4000,"<>0"))

Regards

 

[NARAYANK991]

Hi ,

This does away with the error ; I have not checked whether it replicates the behaviour of your formula.

Function AvailCol(nAvail) As String
    Dim cCol As Integer 'column in which availability should be found
    Dim nRows As Integer 'number of rows to be included in range
    
    With ThisWorkbook.Worksheets("CoE Manager Data")
         cCol = .Range("$C$2:$U$2").Find(Left(nAvail, 3)).Column
         nRows = WorksheetFunction.CountIf(.Range("$B$3:$B$4000"), "<>0")
    
         AvailCol = .Range(.Cells(3, cCol), .Cells(nRows, cCol)).Address
    End With
End Function

 

[pjmorris]

brilliant, worked a charm!

Obviously it was the unqualified Cells functions that were failing!

Thanks again for prompt reply, much appreciated.