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)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]V.01 Revenue - Sale of Goods[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4-1020[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4-1030[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4-1060[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4-1070[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4-8400[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]V.02 Revenue - Rent received[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4-8450[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4-8460[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4-8500[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]V.04 Revenue Handling commission received[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]4-8030[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4-8040[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4-8045[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]4-8047[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]4-8095[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 120"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]V.01 Revenue - Sale of Goods[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4-1020[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4-1030[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4-1060[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4-1070[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4-8400[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]V.02 Revenue - Rent received[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4-8450[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4-8460[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4-8500[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]V.04 Revenue Handling commission received[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]4-8030[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4-8040[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4-8045[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]4-8047[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]4-8095[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 120"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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())