Finding Value in Last Cell and Comparing Data to Run Macro

Question

*EDIT Here is what ended up kind of working. The solutions below do not run the AddProj when new row is inserted.

Sub Worksheet_Calculate()

Dim X As Range
Set X = LastCell 'The X is superflous, you could just use the LastCell variable
    If Sheet5.Range("A" & Rows.Count).Value < X.Value Then
        X.Value = Me.Range("A" & Rows.Count).Value
        AddProj
    End If
End Sub

Module 1 contains the following:

  Function LastCell() As Range
        With Sheet5
            Set LastCell = .Cells(Rows.Count, 1).End(xlUp)
        End With
    End Function
Sub AddProj()

Sheet1.Range("Master").Copy Sheet1.Range("C" & Rows.Count).End(xlUp).Offset(1)

End Sub

I am trying to read the data in the last cell of a column. The value of "X" should be the value of this last cell. I then want "X" to be compared to the number of rows and if the number of rows is less than "X", perform my macro "AddProj". Once "X" and Column A are the same value, nothing else is to be done.

For some reason, it is not working. This code is on the worksheet where I want the comparison to be made. Please see my code below:

Private Sub Worksheet_Calculate()

X = LastCell

If Sheet5.Range("A" & Rows.Count).Value < Sheet5.Range("X").Value Then
  Sheet5.Range("X").Value = Me.Range("A" & Rows.Count).Value
  AddProj
End If


End Sub

Sub LastCell()
Range("A1").End(xlDown).Select

End Sub

The "AddProj" is a module that is referenced in the code above (thank you @jsheeran @SJR ACyril for help):

Sub AddProj()

Sheet1.Range("Master").Copy Sheet1.Range("C" & Rows.Count).End(xlUp).Offset(1)

End Sub

Thanks in advance.

score 0 · Answer 1 · answered Mar 05 '18 at 22:32

Try this:

Sub Worksheet_Calculate()
Dim lRow As Long
lRow = Sheet5.Cells(Sheet5.Rows.Count, 1).End(xlUp).Row
If Sheet5.Cells(lRow, 1) > lRow Then
    Sheet5.Cells(lRow, 1) = lRow
    AddProj
End If
End Sub

X is a variable but you refer to it as "X". Also avoid using .Select as it is not necessary and even in this case just does nothing, because first of all a Sub cannot return a value and second .Select has also no return value. The best way to calculate the last row is this: Sheet5.Cells(Sheet5.Rows.Count, 1).End(xlUp).Row

score 0 · Answer 2 · answered Mar 06 '18 at 04:49

0

Here is just a slight variation on UPGs great answer.

Dim lRow As Long
lRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row

    If lRow >= Sheet1.Cells(lRow, 1) Then
        Exit Sub
    Else: AddProj
    End If

answered Mar 06 '18 at 04:49

GMalc

2,608
1
9
16

Neither of your solutions actually ran AddProj to paste the template in Sheet1. See above for what I did. – Remi Mar 06 '18 at 16:58
I tested the above with mock data according to your description; i.e. If the number of rows is <= to the value in the last cell in the column, then ran the "AddProj" macro. It work perfectly in a workbook module. Yes, neither of these macros will work for you because, i think you should be using Worksheet_Change event, due to your statement "The solutions below do not run the AddPro' when new row is inserted". Worksheet_Calculate runs when a cells value, that is used in a formula, changed; Thus causing the recalculation of the worksheet. Adding a row may not activate any formulas. – GMalc Mar 06 '18 at 18:37
Check out this SO question, it will help you understand Worksheet_Calculate [Click on this link](https://stackoverflow.com/questions/4388279/how-can-i-run-a-vba-code-each-time-a-cell-get-is-value-changed-by-a-formula) – GMalc Mar 06 '18 at 19:02

Finding Value in Last Cell and Comparing Data to Run Macro

2 Answers2