Find the cell with the last "X" in a column

[ebrandt]

What would be the VBA code to find the last "X" in a column starting at cell E4 and looking as far as E350? Assume that the X's will be continuous from E4 without leaving blanks until the end.

Is there also a formula that could do that?

 

[Joe4]

Could there be other entries in these cells that are NOT "X" that we would need to ignore?
Or, will it just be "X" or blank?

 

[ebrandt]

The cell will contain the following formula =IF(LEN(C5)>0,"X","")

so either an X or a blank produced by ""

 

[Joe4]

So, does:
cell E4 look at cell C4,
cell E5 look at cell C5,
cell E6 look at cell C6,
etc?

If that is the case, can't we simply look in column C to see where the last entry occurs, i.e.

Dim lastRow as Long
lastRow = Cells(Rows.Count,"C").End(xlUp).Row

 

[Scott Huish]

This formula will do it:
=LOOKUP(2,1/(E4:E350="X"),ROW(E4:E350))

 

[ebrandt]

I appreciate the help.................

We can look at column C instead, but I only want to go between cells C4 and C350 and there will be either text or a formula in cell C4:350 as well. (this is the formula: ='Spec Section Transfer'!C3)

<tbody> </tbody>

<tbody>
</tbody>

 

[Joe4]

See Scott's reply. That should do what you want.

 

[ebrandt]

Thanks Scott!