Hello there experts,
I have the following code that successfully returns the values of any cells in column A starting with a letter A to Z:
=IF(AND(CODE(UPPER('PL & BS Leadsheets'!A5))>64,CODE(UPPER('PL & BS Leadsheets'!A5))<91),'PL & BS Leadsheets'!A5,0)
The range in sheet 'PL & BS Leadsheets' looks like this in column A (this is just a snip because it won't let me copy images onto here)
<tbody>
</tbody>
<colgroup><col></colgroup><tbody>
</tbody>
However, the PROBLEM is that I will have 0's returned whenever that formula returns FALSE when it encounters the cells that don't start with a letter (i.e. the rest of the cells with a code which there is more of hence filling my worksheet with more 0 values than what I actually want returned (to see) which is the category headers (i.e. those starting with a letter).
My question: Is there a way to loop through that range in column A to skip through and check the next row (i.e. 'PL & BS Leadsheets'!A6, then 'PL & BS Leadsheets'!A7, and so on) when the formula returns a false instead of displaying a 0 each time? I understand this might take vba which I am fine with.
I am not great with vba but I am thinking possibly there could be a macro function added to the formula in place of 0 (e.g. nextrow()), then in the module of vba, the code to loop through my range.
i.e. =IF(AND(CODE(UPPER('PL & BS Leadsheets'!A5))>64,CODE(UPPER('PL & BS Leadsheets'!A5))<91),'PL & BS Leadsheets'!A5,nextrow())
I have the following code that successfully returns the values of any cells in column A starting with a letter A to Z:
=IF(AND(CODE(UPPER('PL & BS Leadsheets'!A5))>64,CODE(UPPER('PL & BS Leadsheets'!A5))<91),'PL & BS Leadsheets'!A5,0)
The range in sheet 'PL & BS Leadsheets' looks like this in column A (this is just a snip because it won't let me copy images onto here)
1 | V.01 Revenue - Sale of Goods |
2 | 4-1020 |
3 | 4-1030 |
4 | 4-1060 |
5 | 4-1070 |
6 | 4-8400 |
7 | |
8 | V.02 Revenue - Rent received |
9 | 4-8450 |
10 | 4-8460 |
11 | 4-8500 |
12 | |
13 | V.04 Revenue Handling commission received |
14 | 4-8030 |
15 | 4-8040 |
16 | 4-8045 |
17 | 4-8047 |
18 | 4-8095 |
19 |
<tbody>
</tbody>
<colgroup><col></colgroup><tbody>
</tbody>
However, the PROBLEM is that I will have 0's returned whenever that formula returns FALSE when it encounters the cells that don't start with a letter (i.e. the rest of the cells with a code which there is more of hence filling my worksheet with more 0 values than what I actually want returned (to see) which is the category headers (i.e. those starting with a letter).
My question: Is there a way to loop through that range in column A to skip through and check the next row (i.e. 'PL & BS Leadsheets'!A6, then 'PL & BS Leadsheets'!A7, and so on) when the formula returns a false instead of displaying a 0 each time? I understand this might take vba which I am fine with.
I am not great with vba but I am thinking possibly there could be a macro function added to the formula in place of 0 (e.g. nextrow()), then in the module of vba, the code to loop through my range.
i.e. =IF(AND(CODE(UPPER('PL & BS Leadsheets'!A5))>64,CODE(UPPER('PL & BS Leadsheets'!A5))<91),'PL & BS Leadsheets'!A5,nextrow())