Grab last value from a worksheet using a function 2

[DPlank]

I need to code a VBA function I can call from a worksheet to get the value of a cell on a different worksheet in the same workbook.

I'm getting a #NAME! error when I try it.

My code looks like this:

Function FindLastValueInA()
r = Worksheets("Amendments Log").UsedRange.Rows.Count
cellref = "$A$" & r
FindLastValueInA = Range(cellref).Value
End Function

and I'm calling this from a cell on a sheet called "Atherstone" using :

=FindLastValueInA

The code is held in a module rather than in ThisWorkbook or any particular sheet.

What am I doing wrong?

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[Zathras]

Try this variation:

Function FindLastValueInA()
  Application.Volatile
  r = Worksheets("Amendments Log").UsedRange.Rows.Count
  cellref = "$A$" & r
  FindLastValueInA = Range("'Amendments Log'" & "!" & cellref).Value
End Function

Your technique to determine last row assumes there is data in row 1. But you probably know that already.

 

[SkipVought]

Hi,

You ought to be passing the sheet name and column of interest...

Function FindLastValueInA(sSheetName As String, vCol)
    Dim r As Long
    With Worksheets(sSheetName)
        r = .Cells(.Cells.Rows.Count, vCol).End(xlUp).Row
        FindLastValueInA = .Cells(r, vCol).Value
    End With
End Function

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[DPlank]

I solved this just before these replies came through using the following :

Public Function FindLastValueInA(shName)
r = Worksheets(shName).UsedRange.Rows.Count
cellref = "$A$" & r
FindLastValueInA = Worksheets(shName).Range(cellref).Value
Worksheets("Atherstone").Calculate
End Function

Took me a bit of time - been too long since I had any VBA work to do. Thanks for the help guys. Star each, I think.

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[SkipVought]

CAVEAT with UsedRange:

If you DELETE rows,

r = Worksheets(shName).UsedRange.Rows.Count

can give you an erroneous result.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}