How to insert an alphabetic reference in a cell depending on format of another cell.+

[barleyhill]

I have got 100's of pages all in the same format.

Please look at the information in column 'C'. If the text is formatted in a certain way, I wish to insert an alphabetic reference in column B.

So referring to screen shot 1, anywhere a cell contains text with "NO underline" I wish to give an alphabetic label in column B.

I also require every page to re-start the sequence at A.



If the correct formula is used in column B cells, screen shot 2 shows the desired effect.

So referring to screen shot 2 in the example, if I used the correct formula, cell B21 would contain the letter B, and as a result cells B23, B25 and B27 would be corrected automatically in the sequence.

Edit : 13/November/2012 - Excel file added

[zbor]

Wellcome barleyhill,

please next time upload example EXCEL workbook rather than picture.

try this code:

Please Login or Register  to view this content.

[barleyhill]

Thanks zbor for your quick response.

However could I ask you to help me with one more edit.

I need to assume that there is no letters in column B. Your macro works perfect, but only if I have an alphabetic entry already in column B.

Kind regards

Excel query 2.JPG

[zbor]

What code did you test?
I've edit it at 10:42.
Did you try that one or previous one?

[barleyhill]

Im pretty sure I've used the edit.

Also, if I add new pages to the document and re-run the macro, it only runs the macro if I have a letter against one of the non-underlined entries.

[zbor]

I don't get it.. It should work only for non-blank and non-underlined cells.

[barleyhill]

Forgive me, I am just learning to use macros.

When I run the macro you have provided in Book1 I get an error message saying "Run-time error '9' Subscript out of range.

If it makes it easier;
1. using two columns A and B.
2. If there is data (not blank) in a cell in column B then I require it to be referenced with the letter A in the corresponding cell in column A.
3. The referencing to be restarted at A on every new page.


I apologize if I am making a fundamental error with the info you are providing me.

[zbor]

I didn't previously get this error because you need to select one cell at the end of document first so it can count breaking points up to that page.
So I inserted Range("B" & LR).Select part and now it's working.
If you want to return at the beggining of document use Range("B1").Select
If you want to remain to the last cell remove this row rom the code.

Please Login or Register  to view this content.

[barleyhill]

Hi Zbor,

Thank you for the update.

However as I ran the macro, column A was referencing text which was not underlined in other columns also. (Not just column B).

I have added an excel file to my original post, which I think is much more useful with "before" and "after" macro. I think many quantity surveyors worldwide will benefit from this macro in the production of Bills of Quantities. Many companies pay money for software to do these functions, however I have discovered with macros, this is possible and more cost effective in Excel.

Basically, if an entry in column C, then reference the item in column A alphabetically, restarting after every page break.

Thanks again for your help.

[zbor]

As I told you in post 2: uploading example workbook will bring you solution much quicklier.

Try this for checking non-underline in B column AND non-blank cell in C column:

Please Login or Register  to view this content.

[zbor]

Or you want for C column no matter what's in cell?

Please Login or Register  to view this content.

[barleyhill]

Zbor for President!!

This is brilliant. Thanks very much. Yes your last post works perfect for me! Hours saved!

My question has been resolved.

I have one more small query. Sometimes the page breaks are out of order if new rows are added to the sheet. Therefore this effects my macro as it starts my alphabetic referencing on every page with the letter A.

To solve this I would be grateful for the macro to first correct the page breaks. The page breaks should occur after a cell in column B, only if it contains either the word 'To Collection' or 'Forward to Summary'. (The cell may contain other words eg Forward to Summary Number 1, Forward to Summary Number 2 etc.)


Thanks again, this has been immense help!

[zbor]

Is it enough that start after Description in column B?

Please Login or Register  to view this content.

Or you should write all possibilites in column B that could occure.
Or you can write unique criteria in next cell in column C (for example: End Page).
So code won't even look for Page breaks but it will look where End Page write in Column C?

[barleyhill]

The above didn't insert page breaks in the correct place.

Everytime "Description" appears the page break should be inserted over the row above it. But I believe this would be a very complicated macro as "Description" is only in Row 2 which is repeated in the "rows to repeat at top" printing function.

Is it possible that excel looks at all the text in cells in row 'b'. If the words "To collection" or "Forward to Summary" are included amongst the words in the cell, then a page break is inserted below this cell? These are the only two situations where i require a page break. (I'm sure the macro would have to start by removing all the previous page breaks too?)

[zbor]

I did this but I need to go now..
Anyway it fail on last page (if there is no page after last word)...

But really can solve it now.