Assistance setting up a "last row" macro

[londa_vba]

Hello
I found a formula that correctly identifies my last row with value results. I used the record macro feature to convert it to vba code. Now I need help piecing it together.
I am trying to identify the last row with value results and then add the text "AL" to the cell directly beneath it.

the formula that worked for me is =LOOKUP(2,1/((C:C)<>""),ROW(C:C))
when converted to vba, I got

Formula2R1C1 = "=LOOKUP(2,1/((C[-3])<>""""),ROW(C[-3]))"

That's as far as I have been able to take myself...

 

[Joe4]

If you simply want to add the text "AL" under the last cell in column C with data, use this one line of code instead:

Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = "AL"

 

[londa_vba]

If you simply want to add the text "AL" under the last cell in column C with data, use this one line of code instead:

Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = "AL"

Hi Joe,
Your code added the text into row 97. My formula indicates the last row is 43 which is the last row I want it to identify. I am not sure if that is because I am using formulas within the cell and have copied and pasted data all over this sheet.

 

[Joe4]

Ah, so the other cells really are not blank - they have formulas that are not returning any values.
Try this instead:

Dim lr as Long
lr = Columns("C").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
Range("C" & lr + 1).Value = "AL"

 

[londa_vba]

Ah, so the other cells really are not blank - they have formulas that are not returning any values.
Try this instead:

Dim lr as Long
lr = Columns("C").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
Range("C" & lr + 1).Value = "AL"

This worked perfect. This is so exciting! Thank you so much.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[BigBeachBananas]

Hi @Joe4,

What does Find("*") do?

 

[Joe4]

Hi @Joe4,

What does Find("*") do?

Looking for a non-blank value.

 

[BigBeachBananas]

Looking for a non-blank value.

Would this work in Ctr+F, or only in VBA?

If not, is there a way to search the next non-blank cell in Ctrl+F?

 

[londa_vba]

Ah, so the other cells really are not blank - they have formulas that are not returning any values.
Try this instead:

Dim lr as Long
lr = Columns("C").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
Range("C" & lr + 1).Value = "AL"

If I needed to take this code one step further and have the value = to "AL" PLUS the value in the cell two columns to the left (column A) of whatever that last row is. How would I achieve that?