PDA

View Full Version : Solved: Making room for a date



Paleo
12-21-2005, 11:30 AM
Hi guys,

I have a list of dates, but some out of them are not being shown, so I need to make room for that date.

I am using the following code:


Dim j As Long, k As Date
k = Range("B" & Rows.Count).End(xlUp).Value
For j = Range("B" & Rows.Count).End(xlUp).Row To 1 Step -1
If Range("B" & j - 1).Value < k And Range("B" & j).Value > k Then
With Range("B" & j)
.Select
.EntireRow.Insert
.Value = Range("B" & j + 1).Value - 1
End With
k = k - 1
End If
Next

:banghead: :banghead: :banghead: :banghead:
What do I want? Ok, I want it to add a row when the currente row is 1/5/2005 and the row after that is 1/3/2005 and then it need to write 1/4/2005 to the newly created row. Was I clear?

Any clues? :dunno :dunno

Bob Phillips
12-21-2005, 12:29 PM
Paleo,

Try this



Sub AddDate()
Dim j As Long, k As Date
For j = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
k = Cells(j, "B").Value
If Range("B" & j - 1).Value < k - 1 Then
Do
Rows(j).EntireRow.Insert
Cells(j, "B").Value = Cells(j + 1, "B").Value - 1
Loop Until Cells(j, "B").Value = Cells(j - 1, "B").Value + 1
End If
Next
End Sub

Paleo
12-22-2005, 05:07 AM
Thanks, I will test it.

Paleo
12-22-2005, 12:25 PM
Nope, didnt work yet. Got the same result as using my code. In column B I have dates: 1/7/2005, 1/14/2005, 1/18/2005, 1/25/2005.

The problem is that I cant let it jump any day, it must find which ones are not there and complete the sequence: 1/7/2005, 1/8/2005, 1/9/2005, ...

Bob Phillips
12-22-2005, 01:38 PM
I tested with those 4 dates andf it gave me

07-Jan
08-Jan
09-Jan
10-Jan
11-Jan
12-Jan
13-Jan
14-Jan
15-Jan
16-Jan
17-Jan
18-Jan
19-Jan
20-Jan
21-Jan
22-Jan
23-Jan
24-Jan
25-Jan
what should I get?

Paleo
12-22-2005, 01:51 PM
Very strange because thats exactly what I want but it still making no changes here...

Paleo
12-26-2005, 08:12 AM
Ok, I finally got it working with this changes:


Dim j As Long, k As Date
k = Range("B" & Rows.Count).End(xlUp).Value
For j = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("B" & j - 1).Value < k - 1 Then
Rows(j).EntireRow.Insert
Cells(j, "B").Value = k - 1
j = j + 1
End If
If Range("B" & j - 1).Value <> k Then k = k - 1
Next


Thanks for the help xld!