macro to copy formula up to before first blank row for each section

[Howardc1001]

I have formulas in G7, G20 & G33

I have written code to copy the data to the relevant cells

I would like my copy amended as follows:

1) Copy G7 and paste into the last cell in col G before first blank i.e G8:G14

2) Copy G20 and paste into the last cell in col G before first blank i.e G21:G27
3) Copy G33 and paste into the last cell in col G before first blank i.e G34:G40



It would be appreciated if someone could kindly amend my code below

Please Login or Register  to view this content.

[Bo_Ry]

Please try formula at G6

=IF(B6="","",IF(B6="Balance","Branch Codes",INDEX(A3:A$4,MATCH("Z",B5:B$6))))

or

Please Login or Register  to view this content.

[Howardc1001]

Thanks for the help

what is the significance of code below ?

[code[ MATCH(""Z"" [/code]

[Howardc1001]

Hi Bo_Ry

Just following up whether you have seen my question regarding the significance of code below ?


[code[ MATCH(""Z"" [/code]


Kindly advise why the "Z" is used i.e the purpose of this

[bebo021999]

Maybe he is off for a while, i will try to help you.
Let say formula in row 22
the main part is:
INDEX(A$4:A19,MATCH("Z",B$6:B21)))
In which, try to find position of cell with "Balance", use this to get value in column A.
Since target value in column A always is 2 rows before the "balance" then you can see the starting point range of INDEX (from row 4) is smaller than range in MATCH (from row 6)

as you know, the MATCH function:
=MATCH(criteria_value,range,n)
with n =0 (exact match) and 1 (or obmit) (not exact match)

with range B$6:B21, it will be array of a text ("balance") and numbers
to get the LAST non-numeric in that range, MATCH will try to search for the BIGGEST text ( it is "Z", or "ZZZ") to get the nearest text found ("balance")
For example: range A1:A6
100
text A
101
102
text B
103
104
105
then MATCH("Z",A1:A6) = 5 where the last text (text B) found

[Howardc1001]

Many thanks for the help . It makes perfect sense