Results 1 to 5 of 5

Thread: Find matching cell and shift data starting from that point

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2010
    Posts
    6

    Find matching cell and shift data starting from that point

    Hi All -

    I am hoping there is a fairly easy way to perform this. The cells without data could have 0's or blanks so I have to use start date to determine first cell.


    Name:  example.jpg
Views: 324
Size:  51.7 KB


    Thank you so much!!!

  2. #2
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    946

    Re: Find matching cell and shift data starting from that point

    Hi maybe a little clarification on whats going on here would be helpful, i have no idea what your table is trying to represent but i see what your after

    well there is several ways to do this

    get the range of the table data (all the cells with amounts in), then do a quick loop starting on the left side and then maybe delete blanks and shift left until u find a value or maybe check the cell if its empty move right until you find a value then copy paste or assign the first blank with the first encountered value then restart the loop. hopefully you will get an idea

    etc.etc
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2010
    Posts
    6

    Re: Find matching cell and shift data starting from that point

    Thanks. I need to use the start date to find the correct 'starting point' Some of the cells might have 0 or smaller amounts so I cant just look for a value or non empty cell. I am thinking I want to leave the original data as is and first thing is to copy it all to a new sheet before manipulating the data. Basically I need to match the start date to the correct column, use that as the first cell and then shift all the data to the left and change the headers to month1, month2 rather than the actual date. I am not sure how to use the date to find my starting point.
    Any example code would be helpful.

    Thank you again.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jun 2010
    Posts
    6

    Re: Find matching cell and shift data starting from that point

    This seems to be working the way I need the data to shift but rather than finding the first cell with a numeric value, can this be tweeked so the first month is from the column that matches the student startdate rather regardless of other cells?

    Thank you.


    Code:
    Sub ShiftData()
    Dim lr As Long, lc As Long, i As Long, j As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    lc = Cells(2, Columns.Count).End(xlToLeft).Column
    Application.ScreenUpdating = False
    For i = 3 To lr
        For j = 3 To lc
            If Not IsNumeric(Cells(i, j)) Or Cells(i, j) = 0 Then
                Cells(i, j).Delete shift:=xlToLeft
                If j < Cells(i, Columns.Count).End(xlToLeft).Column Then j = j - 1
                lc = Cells(i, Columns.Count).End(xlToLeft).Column
            End If
        Next j
    Next i
    lc = Cells(2, Columns.Count).End(xlToLeft).Column
    With Range(Cells(3, "C"), Cells(lr, lc)).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    Range("C1").FormulaR1C1 = "Month 1"
    Range("C1").AutoFill Destination:=Range(Cells(1, "C"), Cells(1, lc)), Type:=xlFillDefault
    Application.ScreenUpdating = True
    End Sub

  5. #5
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    946

    Re: Find matching cell and shift data starting from that point

    if you use the values of the dates you can easily do a quick check of where they should be placed, run through each cell in the row and sort the dates values, highest first should sort it correctly. by value i mean the internal date value, it should be around 40000.
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


Posting Permissions

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



Click Here to Expand Forum to Full Width