How to loop though a range of cells and if it is false to skip to search the next row, rather than insert my false result

[Eyale]

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())

 

[Eyale]

I tried this formula from this thread - https://www.mrexcel.com/forum/excel...nt-false-check-next-row-true-condition-2.html

=IFERROR(INDEX('PL & BS Leadsheets'!A$5:A$191,SMALL(IF(AND(CODE(UPPER('PL & BS Leadsheets'!A$5))>64,CODE(UPPER('PL & BS Leadsheets'!A$5))<91),ROW('PL & BS Leadsheets'!A$5:A$191)-ROW('PL & BS Leadsheets'!A$5)+1),ROW('PL & BS Leadsheets'!A1))),"")

It was close but did not work exactly like it was supposed to. Was giving blanks whenever the argument gave a false