Why do I get different results of Lastcell?

[Oberon70]

Hi,

I have the below code, which for some reason gives me the result of $A$1 instead of $AK$28, which is the last cell for the current statement I am testing with.

Sub CopyTbleToTmpSht()

Dim LastCell As String
Dim oarray As Variant


With wsData
    LastCell = wsData.Range(FindLast(3)).Address
End With

oarray = wsData.Range("A1:" & LastCell)

wsTmpData.Range("A1:" & LastCell) = oarray

Debug.Print LastCell

'wsData.Range("A1:" & LastCell).Select


End Sub

It will work if I change the code to the below, but was wondering why I have to activate the sheet for it to work? Is there a way when I call the function it is pointing to the worksheet I want?

Sub CopyTbleToTmpSht()

Dim LastCell As String
Dim oarray As Variant


With wsData
    .Activate
    LastCell = wsData.Range(FindLast(3)).Address
End With

oarray = wsData.Range("A1:" & LastCell)

wsTmpData.Range("A1:" & LastCell) = oarray

Debug.Print LastCell

'wsData.Range("A1:" & LastCell).Select


End Sub

 

[6StringJazzer]

FindLast is not a VBA function. You must have code for that somewhere, and the answer to your question is probably in that code. Can you post it here? I am guessing it looks at ActiveSheet for the cell it wants. I am sorry to say that is an undesirable coding technique, for the very reason you are experiencing.

By the way, the original version of your code does not take advantage of the With statement.

With wsData
    LastCell = wsData.Range(FindLast(3)).Address
End With

I would not recommend a With here because there is only one reference to wsData, but if you want to do it you would do this:

With wsData
    LastCell = .Range(FindLast(3)).Address
End With

 

[Oberon70]

thanks I will remove the with, below is the Function Findlast, which was provided to me.

Enum XLFindLast
    xlFindLastRow = 1
    xlFindLastColumn
    xlFindlastCell
End Enum

Function FindLast(lRowColCell As Long, _
                    Optional sSheet As String, _
                    Optional sRange As String)
'Find the last row, column, or cell using the Range.Find method
'lRowColCell: 1=Row, 2=Col, 3=Cell

Dim lRow As Long
Dim lCol As Long
Dim wsFind As Worksheet
Dim rFind As Range

    'Default to ActiveSheet if none specified
    On Error GoTo ErrExit
    
    If sSheet = "" Then
        Set wsFind = ActiveSheet
    Else
        Set wsFind = Worksheets(sSheet)
    End If

    'Default to all cells if range no specified
    If sRange = "" Then
        Set rFind = wsFind.Cells
    Else
        Set rFind = wsFind.Range(sRange)
    End If
    
    On Error GoTo 0

    Select Case lRowColCell
    
        Case 1 'Find last row
            On Error Resume Next
            FindLast = rFind.Find(What:="*", _
                            After:=rFind.Cells(1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
            On Error GoTo 0

        Case 2 'Find last column
            On Error Resume Next
            FindLast = rFind.Find(What:="*", _
                            After:=rFind.Cells(1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
            On Error GoTo 0

        Case 3 'Find last cell by finding last row & col
            On Error Resume Next
            lRow = rFind.Find(What:="*", _
                           After:=rFind.Cells(1), _
                           LookAt:=xlPart, _
                           LookIn:=xlFormulas, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlPrevious, _
                           MatchCase:=False).Row
            On Error GoTo 0

            On Error Resume Next
            lCol = rFind.Find(What:="*", _
                            After:=rFind.Cells(1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
            On Error GoTo 0

            On Error Resume Next
            FindLast = wsFind.Cells(lRow, lCol).Address(False, False)
            'If lRow or lCol = 0 then entire sheet is blank, return "A1"
            If Err.Number > 0 Then
                FindLast = rFind.Cells(1).Address(False, False)
                Err.Clear
            End If
            On Error GoTo 0

    End Select
    
    Exit Function
    
ErrExit:

    MsgBox "Error setting the worksheet or range."

End Function

 

[6StringJazzer]

As I suspected, if you do not provide a sheet name, LastCell assumes you want to reference ActiveSheet. Change your line of code as follows:

    LastCell = wsData.Range(FindLast(3, wsData.Name)).Address

 

[johnnyL]

Perhaps you could provide what wsData equals.

That is a lot of code just get a result.

 

[johnnyL]

Are you just looking for the last used cell in the sheet?

 

[johnnyL]

The following is a test you can run which I think will give you the result you are looking for with much shorter code:

Sub Test()
'
    Dim ColValue    As Range
    Dim RowValue    As Range
    Dim wsData      As Worksheet
'
    Set wsData = Sheets("Sheet1")                                         ' <--- set this to the proper sheet name
    Set ColValue = wsData.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)
    Set RowValue = wsData.Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
'
Debug.Print "ColValue.Address = " & ColValue.Address
Debug.Print "RowValue.Address = " & RowValue.Address
'
MsgBox "ColValue.Address = " & ColValue.Address & vbCrLf & "RowValue.Address = " & RowValue.Address
End Sub

One of those two results should give you the result you are looking for, I am guessing it is the second result that you want.