Reference to next non-blank cell

  • Thread starter Thread starter Hilvert Scheper
  • Start date Start date
H

Hilvert Scheper

Hi there Dear specialists,

Can I Please have some help with this;
I want a Formula that finds and returns the Text-string from the next
Non-Blank Cell in the previous column. The reason why I'm asking is that this
is part of a Pivot-Table due to which some cells are empty and the number of
empty cells is Variable.

Many Thanks for Your help, I hope this example helps:

Column A Column B
(blank) This Cell (B1) needs to find the next non-blank cell in column
A,
(blank) and return text-string "SN2 2QH" from Cell A4
(blank)
SN2 2QH

Thank You again,
Hilvert
 
Dear Roger,
Thank You for Your advice,
I am Sorry for not making myself understood here, my apologies for the
misunderstanding. My question is part of whet I need to include in a Macro.
The Macro creates the Pivot-Table, then Copies the pivot table and pastes
Values so that I can then work with the data retrieved from a Database, and
The data that I need to calculate is and can not be retrieved in this
pivot-table.
The problem here is that I need to make calculations using data in one row
and comparing it against the next non-blank cell in a column....

Still doesn't make it any easier I presume, sorry, and Many Thanks for Your
attention again!
Hilvert

Roger Govier said:
Hi Hilvert

You need to use the GetPivotData function.
For help on this take a look at
http://www.contextures.com/xlPivot06.html
Click to expand...

Many Thanks for Your help, I hope this example helps:

Column A Column B
(blank) In Cell B1 I need to find the next non-blank cell in
(blank) column A, and return text-string "SN2 2QH" from
Cell A4
(blank)
SN2 2QH

Thank You again,
Hilvert
 
Hi Hilvert

Difficult to understand what you mean, without seeing the PT layout and the
macro.
If you want to mail me a copy of the workbook I will take a look
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address
 
Hi Roger,
Many Thanks for Your reply,
I have replied to You as You suggested.
Hope You can help me!!
Rgds,
Hilvert
 
Hi All,
having done some investigations myself, I have Not found a solution for my
problem using a Formula, However I have found a way around it using a Macro
instead.
Basically Move a cell to the left, find a non-Blank cell, copy and paste
into the cell where You started.
A Most Warmhearted Thank You to Roger for trying to help!
Hilvert

The Macro Code I used is:

alphabet1 = ActiveCell.Address
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
ActiveCell.Offset(0, -1).Select
Selection.Resize(numRows + 20, numColumns).Select
Selection.Find(What:="*", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Select
alphabet22 = ActiveCell.Address
Selection.Copy
Range(alphabet1).Select
ActiveSheet.Paste
 
Back
Top