• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using LastRow when looping through worksheets

M

msquared99

Member
OK I have this code and all I want it to do is find the last cell in column P, which has a sub-total in it and reference that cell in another worksheet in the workbook.

My issue is the message box says LastRow = 1, it should be 4121 in this example

Worksheet M3 has the cell I want to reference. The column header is in cell P5 and the data begins in cell P6 and ends randomly.

What I want to see in the worksheet Commission Summary is in cell B2 ='M3"!P4121

P4121 would be random in length and contains a sub-total. So I do not want a hard coded value in cell B2 of the Summary worksheet.

Code: 
Sub test()
Dim wsM3 As Worksheet, wsCS As Worksheet
Dim LastRow As Long
Set wsM3 = Worksheets("M3")
Set wsCS = Worksheets("Commission Summary")
With wsM3
  LastRow = Cells(.Cells.Rows.Count, "P").End(xlUp).Row
  
End With
wsCS.Range("B2") = "=sum(M3!P" & LastRow & ")"
MsgBox LastRow
LastRow = 0
End Sub
 
Code: 
   With wsM3
  LastRow = .Cells(.Rows.Count, "p").End(xlUp).Row
  End With
One minore mistake I think
 
As mohadin pointed out, you're missing the leading period on the Cells object. W/o it, the Cells refers to active sheet. With it, refers to the wsM3 (correct).
 
You must log in or register to reply here.
Back
Top