+ Reply to Thread
Results 1 to 9 of 9

Loop - Do until based on the numbers of rows from database

  1. #1
    Registered User
    Join Date
    09-20-2020
    Location
    Curitiba
    MS-Off Ver
    365
    Posts
    7

    Loop - Do until based on the numbers of rows from database

    Hey folks,

    So I am having some problems to find out a code that could help me to complete a simple task. I basically have a gigantic database held by an independent range of formulas. Everytime that I refresh the database (using another code that I created) new lines are added to it. However, the independent range of formulas that I've just mentioned, does not follow the change (obviously), and that's where my problems begin.

    I want to create a loop that copy a range of formulas from the cells above, and do this action based on the numbers of rows that my database has.

    Do you guys have any ideas?
    Last edited by Galopes; 06-21-2021 at 06:34 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Loop - Do until based on the numbers of rows from database

    If you could attached a sample workbook as stated in yellow above this page, it would help get you answers and examples.
    Sound like you would need a variable to hold the record count from the database to use as a part of a dynamic range so that the formulas are applied down..
    If you find the suggestion or solution helpful, please consider adding reputation to the post.

  3. #3
    Registered User
    Join Date
    09-20-2020
    Location
    Curitiba
    MS-Off Ver
    365
    Posts
    7
    Quote Originally Posted by cubangt View Post
    If you could attached a sample workbook as stated in yellow above this page, it would help get you answers and examples.
    Sound like you would need a variable to hold the record count from the database to use as a part of a dynamic range so that the formulas are applied down..
    Unfortunately I cannot send you guys a sample . But the attached picture could be helpful. Noticed that there is blank space, a gap, between the last row with formula and the last line of data, from the database. That occured because whenever I refresh the data, the formula does not follow the trend (obviously).

    So the code that I am looking for should loop the formula range until the last database line.
    Attached Images Attached Images

  4. #4
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Loop - Do until based on the numbers of rows from database

    And just to be clear, all this is within an excel worksheet correct

    Here is how i use something similar in my reports.. Obviously adjust the ranges and worksheet names


    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-20-2020
    Location
    Curitiba
    MS-Off Ver
    365
    Posts
    7
    Quote Originally Posted by cubangt View Post
    And just to be clear, all this is within an excel worksheet correct

    Here is how i use something similar in my reports.. Obviously adjust the ranges and worksheet names


    Please Login or Register  to view this content.
    Exactly! All this is within an excel worksheet. Let me try the code.

  6. #6
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Loop - Do until based on the numbers of rows from database

    Now if you already have the formula setup in the cell, you can always find the last row of the formula column and apply the autofill from there .
    Seems there may be 2 ways to do it..

    #1 handle the entire process in VBA, assign the formula and autofill all in the macro
    #2 apply the formula on the sheet/cell then autofill in the macro

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Loop - Do until based on the numbers of rows from database

    You are specifically asking about looping code so the minimum we need is to see what your looping code says.

    Better still upload the workbook, anonymised if necessary. Pictures are rarely much help. Remove unnecessary rows, we don't need to see zillions, just as long as it is truly representative.

    That said, problems with loops are often associated by making changes to rows within the loop. Try processing the loop from the bottom row upwards.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  8. #8
    Registered User
    Join Date
    09-20-2020
    Location
    Curitiba
    MS-Off Ver
    365
    Posts
    7

    Re: Loop - Do until based on the numbers of rows from database

    Chap, amazing solution! I've made some adjustments and it perfectly worked!

  9. #9
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Loop - Do until based on the numbers of rows from database

    If you found the suggestion or solution helpful, please consider adding reputation to the post. Thank you

+ 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] VBA Loop Sequential Numbers X Times Based on Cell Value
    By phelbin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-19-2016, 10:19 PM
  2. Replies: 3
    Last Post: 05-30-2013, 04:29 AM
  3. [SOLVED] Function that lists specific rows of a database based on a cell value
    By setilvenstre in forum Excel General
    Replies: 7
    Last Post: 10-18-2012, 10:39 AM
  4. Replies: 1
    Last Post: 09-19-2012, 09:50 AM
  5. Replies: 8
    Last Post: 04-30-2012, 05:16 AM
  6. condensing long if's based on same numbers (loop)
    By Squirrel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-13-2011, 10:00 AM
  7. Replies: 1
    Last Post: 12-30-2010, 06:49 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