VBA Help - Modify a LastRow Lookup line of code - Excel 2016

[Johnny Thunder]

Hello All,

I have a piece of code that I need to modify since it works so well, it essentially is a Last Row Lookup (Column Based) and defines the last row with an actual formula result.


What I need: I have a variable to replace the column letter "ColumnL" that I need to update the line of code but it doesn't seem to work with different variations that I have tried.

Line: cSht.[match(2,1/(AP:AP<>0))]

cSht = my Variable Sheet

Any help is appreciated.

 

[footoo]

It's not clear what you want to do.
If you want to find the last row number with data in column AP :

LR=Cells(Rows.count,"AP").End(xlUp).Row

 

[Johnny Thunder]

I thought my post was extremely clear. I am looking to modify my already working code to replace the manual "AP" column designation to my dynamic variable "ColumnL"

Also, the code you posted results in 1, there is over 1000 rows of data and the last cell that resulted in a value ends at 617. My line of code works great, I just don't know how to modify it to include my variable instead of hardcoding the range like "AP:AP".

Thank you for trying though. This is one of the final pieces of my code and I am done.

 

[Fluff]

Maybe

Dim Clmn As String
Clmn = "AP:AP"
Debug.Print csht.Evaluate("match(2,1/(" & Clmn & "<>0))")

But it's a very slow way of doing it.

 

[Johnny Thunder]

Woohoo! That is exactly what I needed!

May I ask, whats the line about evaluate? And what was the need for the [ ] brackets used in the original version of the formula? I found it on this site a few days back and wasn't really sure how it worked but it works well.

 

[Fluff]

You're welcome & thanks for the feedback
The [] is a "shorthand" way of using Evaluate, but I don't think you can use it with variables.

 

[footoo]

I thought my post was extremely clear. I am looking to modify my already working code to replace the manual "AP" column designation to my dynamic variable "ColumnL"

Also, the code you posted results in 1, there is over 1000 rows of data and the last cell that resulted in a value ends at 617. My line of code works great, I just don't know how to modify it to include my variable instead of hardcoding the range like "AP:AP".

Thank you for trying though. This is one of the final pieces of my code and I am done.

If the code I posted assigns a value of 1 to LR, either there is no data in column AP or data in AP1 only.
Although your code is now working, it's better to avoid using worksheet formulas in code unnecessarily.
This is better/standard :

Dim Clmn As String, LR#
Clmn = "AP:AP"
LR = Cells(Rows.Count, Clmn).End(xlUp).Row

There are other ways which avoid using a worksheet function :
https://www.google.com/search?q=exc....1.69i57j0.77321j0j8&sourceid=chrome&ie=UTF-8