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

1. ## 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.

Thank you so much!!!

2. ## 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

3. ## 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. ## 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. ## 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.

#### Posting Permissions

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