1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

moving data forward without overwriting previous data

Discussion in 'Software Development' started by newnew, Jan 5, 2015.

Thread Status:
Not open for further replies.
  1. newnew

    newnew Thread Starter

    Joined:
    Jan 1, 2015
    Messages:
    6
    Good Day Everyone:

    I am using Excel 2011.(Mac)
    As (I hope) the example below shows, I am trying to take data (numbers) from 7 cells (such as A1:G1) and then moving that row of data to another location (lets say) A11:G11.
    this process may occur 100 to 1000 times a day and I don't want to overwrite the previous cells of data, rather I want to move new data to the next row. (as in A11:G11 to A12:G12 to A100:G100 and so on)
    Ultimately, at the end of a day, I could potentially view 100+ rows of data - to be analyzed,without overwriting any of the previous rows of data.
    If possible, I do not want to work in VB: if possible, is there a way to work within the basic options of Excel itself?
    I can move the data from A1:G1 to A11:G11, and to A12:G12, but I keep overwriting previous rows of data.
    I have tried advancing the data from one cell (A11) let's say to (A12) by using (A11) +1 which does not work.
    I have tried using if statements such that if +if(A11<>0, A11+1, A11) and similar, but I keep getting circular errors.
    I have tried using if statements to reference cell A11 from (as an example) from A1, but that does not work.
    I don't know how to tell excel that if there is data in a cell such as A11, then to place the data into the next cell - which would be A12.
    OR better yet, how to tell excel that if there is data in A11, or A12 or A...1000 - then place that data into the NEXT cell.
    Sorry, to be so stupid, but I'm really new to anything related to programming: but I am learning. :0)

    If anyone could help, I would be appreciative.
    Thanks everyone,
    J.
    (please see below)




    1 2 3 4 5 6 7 <- Data input on these cells
    (original data input here) A1:G7




    1 2 3 4 5 6 7 <- copy data to another set of cells
    (to view for another application) A7:G7


    1 2 3 4 5 6 7 <- set #1 (I do not want to overwrite the numbers) A11:G11
    11 12 13 14 15 16 17 <- set #2 A12:G12
    21 43 45 56 67 76 32 <- set #3 A13:G13
    <- Perhaps there could be
    <- 200 to 1000 sets ( and so on down the page)
    <- of numbersÂ…

    (It's possible that there could be 1000 rows (lines) of data)
    (which I need to be able to view)
     
  2. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    This would not be possible without using vba.
    The reason for this is that if you used a formula, the formula stays there rather than the value it shows, and as soon as the inputs to the formula change, so does its output value.
    That is, as soon as you change A1, A11, A12, etc would all change to the same thing.

    The VBA you'd need looks like this.
    Code:
    Rem First, we need to find an empty row. This variable will hold the index of the row we want to check next.
    Dim rowCounter
    Rem Start just before the first row we want to check. I'm starting with row 7, so I'm using 6 here.
    rowCounter = 6
    Rem From here to Loop will be repeated until we find what we're after.
    Do
        Rem try the next row
        rowCounter = rowCounter + 1
        Rem check whether the first cell in this row is empty.
    Loop Until IsEmpty(Cells(rowCounter, 1))
    Rem rowCounter now holds the index of the first empty row.
    
    Rem copy the values from the first three cells of the first row to the third row.
    Range("A1:G1").Copy Cells(rowCounter, 1)
    
     
  3. newnew

    newnew Thread Starter

    Joined:
    Jan 1, 2015
    Messages:
    6
    ENT (Josiah)

    Again, I am in debt to Josiah. (The TEACHER - compliment)
    Thank you.
    J.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1140601

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice