Finding Value in Last Cell and Comparing Data to Run Macro

[theteerex]

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:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">PrivateSub 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
EndIf


EndSub

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

EndSub</code>The "AddProj" is a module that is referenced in the code above:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub AddProj()

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

EndSub</code>Thanks in advance.

 

[JLGWhiz]

I am confused. Worksheet_Calculate references sheet5 while AddPro references Sheet1. Why would you add a row to sheet1 and then compare the row count in sheet 5 to a value that I am not sure where you are getting it from. Your LastCell macro does nothing to return a value, it only moves the cursor to a cell somewhere in column A of whichever sheet is active at runtime. If you have a range in sheet5 named "X" then the Range("X") syntax is OK, But if you are trying to use the X value from the X = LastCell, then the sytax should be Range(X), without the quote marks. But it still would not work since the value of X is questionable due to the constuct of the LastCell macro. X will not return the cell selected by the LastCell macro, if that is what was intended. If you can lay our your objective a little clearer without trying to explain the macros, maybe someone can can assist you with developing the code you need.

 

[theteerex]

@JGLWhiz
Sheet5 is a table of information arranged in rows. Each row represents data on different projects.
Sheet1 is a data table used to run pivot table. It pulls in its data from Sheet5 and rearranges it into a form suitable for pivot table to run.
AddProj is a module that automatically copies a template in Sheet1 and pastes it to the first empty row.

The objective I am trying to accomplish is for the AddProj module to run automatically when a user adds a new row in Sheet5.
I am trying to get the code above, Worksheet_Caluclate, to monitor column A, which displays row numbers, and when there is an increase in rows, to automatically run, pasting a new template in Sheet1.
I am trying to do this by defining X as the last cell in column A. Once there is a row added, there will be a difference between X and the value in the row count, triggering the event that runs AddProj.

Hope this clarifies things.

 

[JLGWhiz]

Then you want your LastCell macro to be a Funtion like this

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

PrivateSub 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).[COLOR=#FF0000]Value [/COLOR]< X.[COLOR=#FF0000]Value[/COLOR] Then
        X.[COLOR=#FF0000]Value[/COLOR] = Me.Range("A" & Rows.Count[COLOR=#FF0000]).Value[/COLOR]
        AddProj
    End If
End Sub

The Function goes in the public module1. The worksheet_calculate, as modified above, will call the function and use its value simultaneously, just like a worksheet function in a cell. Note that as in your original code this calss for the value of the last cell, not the row number. If you want the row number, it would be X.Row. But then you could not compare that to the Values as currently defined in the code. Maybe this will get you on track.

 

[JLGWhiz]

If you want to compare row numbers.
To get an integer value for the last row of a column use this method.

Dim lstRw As Long  'Declare long integer
With Sheets("Sheet1")
 lstRw = .Cells(Rows.Count, "A").End(xlUp).Row 'Change the "A" to target Column
End With

 

[theteerex]

@JLGWhiz
Thank you so much.
The code now works when I add in a new row. For some reason, column A in Sheet5 no longer displays the row numbers.
Is there something in the code that makes this so?

Also, if I wanted to try and add code to delete the template should the row be deleted, would that be a new module, called DeleteProj for example?

 

[JLGWhiz]

Nothing in any of the code I provided would change the data in column A of any of the sheets. Are you sure youe were not looking at the row numbers which appear to the left of column A before?
It is not necessary to start a new code module for new code. The thing you have to be careful of is putting the code into the wrong type of module. Event code is normally put in to the code module for the object where the event will occur, ie. Worksheet, ThisWorkbook and UserForm. These codes normally begin with the word 'Private'. Other code which require public access and may require interaction with more than one object such as multiple workbooks and multiple sheets can all be put in code module1. But some people like to use different code modules for different types of code and they might create new code modules to separate the different categories. That is a user preference and not a necessity to make the codes work better or at all. I have about fifty different macros in one of my workbooks that I use when I am interfacing with the forum and they are all in the same module1. In other workbooks where I have programs consisting of several macros, they are all in module1. But, again, it is user preference and if you want to split your macros up and give the modules names, then it will not hurt anything so long as you do not try to run Event macros from the public code modules.

this might give you some insight on modules: https://www.microsofttraining.net/article-1711-what-are-modules-in-excel-vba-coding.html