How to code it in excel?

[Guest]

Copy of message from Excel General Question - 9/26/2006 1:22 AM PST

Does anyone know on how to code on following condition in this group?

[row 1] a blank row
[row 2] 10, which should be equal to the last value in row 3
[row 3] 10
[row 4] a blank row

[row 1] a blank row
[row 2] 23, which should be equal to the last value in row 4
[row 3] 23, which should be equal to the last value in row 4
[row 4] 23
[row 5] a blank row
[row 6] 41, which should be equal to the last value in row 9
[row 7] 41, which should be equal to the last value in row 9
[row 8] 41, which should be equal to the last value in row 9
[row 9] 41
[row 10] a blank row

If the next row [10] is a blank one, then current row [9] is defined to be
the last value.

Does anyone know how to code above condition in excel?
Could anyone give me any suggestion please?
Thank you in advance
Eric

 

[Guest]

set cell = cells(rows.count,1).End(xlup)
msgbox cell.Value

 

[bplumhoff]

Hello Eric,

If your numbers are in A1:A99, then write into B1:
=IF(ISBLANK(A1),"",INDEX(A1:A$99,MATCH("Z","Z"&A1:A$99,)-1))
[Enter as array formula with CTRL + SHIFT + ENTER]
and copy down to B99.

HTH,
Bernd