loop past blank cell

[Zrx]

I have a range A2:A30 and in this range a blank cell may exist.

I am capturing the last used cells value of this range, but need to get past this blank cell. I need to keep the previous cell value and move past the blank cell.

[Mordred]

Hi Zrx, you aren't really giving us much to go on. Are you looping through the range? What is supposed to happen to the cells that aren't empty?

[jeffreybrown]

Something like....

=LOOKUP(9.99999999999999E+307,A:A)

http://www.xldynamic.com/source/xld.LastValue.html

[Zrx]

Hi Zrx, you aren't really giving us much to go on. Are you looping through the range? What is supposed to happen to the cells that aren't empty?

Looping through range to find last value and "trapping" this to varible (completed) but when I encounter a blank cell it will use the previous cell as it is the last cell value so the loop thinks.

Please Login or Register  to view this content.

I am looking into the xlup instead but running into issues.

[TMS]

Please use CODE Tags around your code excerpt.

Regards

[jaslake]

Hi Zrx
Don't pretend to know what you're trying to do.

To find the last populated cell in Column C

Please Login or Register  to view this content.

To find the next unpopulated cell in Column C

Please Login or Register  to view this content.

[Zrx]

Try again to explain...

Range = A2:A33

Cells will have numbers but some cells may be blank. I use the last cell value to subtract a new number from. The issue is when you get a cell in between which is blank the code will take that number above it even if there is non blank cells below.

Not the best explaination I guess

[jaslake]

Hi Zrx

Perhaps an example file coupled with an explanation ?

[Zrx]

Ok, using vba. Find last cell with data (always number). This cell will be subtracted from another. If you look at sample you see yellow cell my current vba stops here as it is last cell, above cell actually. I need a code piece to get past these blank cells but keeping the above cells value so it may be subtracted. This should be a place to start.

[Zrx]

Progress I believe.....

Please Login or Register  to view this content.

Now, I'm working on getting the code to back up one cell (above yellow) cell and "trap" cell value for use else where. This is the next step.

[jaslake]

Hi Zrx

I don't know...based on your sample file...try this

Please Login or Register  to view this content.

[Zrx]

Hi Zrx

I don't know...based on your sample file...try this

Please Login or Register  to view this content.

Yes this finsihed code is working fine, but I see now I need to shift my approach. I need to work across the row instead of the colum. So I have the offset working but fail to get test conditons to work. Can you "nest" trying to below? OR is another approach better.

Please Login or Register  to view this content.

[Zrx]

Please Login or Register  to view this content.

looks like I finally have it. This is achieving what I wish. I do need to tweak the second set of offsets.

If anyone can see ways to improve I'm all ears.