+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,159

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

    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.
    Last edited by Howardc1001; 07-06-2022 at 05:46 AM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,090

    Re: macro to copy formula up to before first blank tow for each section

    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.

  3. #3
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,159

    Re: macro to copy formula up to before first blank tow for each section

    Thanks for the help

    what is the significance of code below ?

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

  4. #4
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,159

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

    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

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,632

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

    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
    Last edited by bebo021999; 07-06-2022 at 10:49 PM.

  6. #6
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,159

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

    Many thanks for the help . It makes perfect sense

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to get Item with section name, when copy item to different section.
    By paulikk in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-17-2021, 07:15 AM
  2. [SOLVED] Macro to copy a section of a worksheet and then loop and copy multiple times
    By Cbird in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-28-2018, 10:41 PM
  3. Replies: 7
    Last Post: 05-14-2017, 10:51 PM
  4. *** Copy sub section total value to main section***
    By prabhubox@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-13-2016, 03:14 AM
  5. VBA macro - Two buttons per section that copy down a row or delete current row
    By JuSteve in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-05-2013, 06:10 PM
  6. Deleting Row Section without Disrupting Formula Range Outside of Deleted Section
    By JeffNYG23 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2013, 09:10 AM
  7. Copy Paste section of macro not working in shared file
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-13-2008, 01:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1