Breaking up the hi-lo series line?

  • Thread starter Thread starter Jim Kelly
  • Start date Start date
J

Jim Kelly

This question is only tangentially about charting, but came up as a
result of my charting project so I hope it's ok to post this here.

I have a table of data that is used to create a VHLC stock chart. The
series of hi-lo has a line which connects all the way across the
chart. I want to insert dates in my table that will break up that
line so that it shows a space on weekends/holidays (ie I only want the
line to show one trade dates. My problem is that my data table only
consosts of trade dates. My thought is to go through the table after
it is created and check for concurrent dates, if a1 and a2 are
concurrent it goes on to compare a2 to a3, until it gets to the bottom
of the date range requested by the user.

I am grabbing the data from MS Access with a program written in VB6
and then inserting it into Excel.
My question is this: How should I structure this loop to go through
and look at two cells, insert a row if necessary and move on to the
next two cells for comparison?
What I've scribbled down is something like this:

Set rng1 = Worksheets("" & SymArray(x) & "").Range("a1")
Set rng2 = rng1.Range("a2")

' For Each a1:b1 In xlWkbNew.ActiveSheet

If DateDiff("d", rng1, rng2) > 1 Then
'need to move down 1 cell then....
ActiveCell.EntireRow.Insert
'select first cell in new row
'set first cell date = date1 +1
End If

' Next a1:b1
Please understand that these are just notes, I realize the code isn't
close to working, but I find it easier to think it through that way :)


I'm doing all of this after the worksheet is filled with the Access
data, but should I do the comparisons as the info is being filled in?
Would that be better programming (more efficient)?
Or should I continue on the way I am working towards with the
compare/insert code coming after ALL of the data has come over from
Access?

Your help is greatly appreciated as always!
Jim
 
This answer worked well, so I'm posting it for future readers:

Public Sub FillInDates()
Dim i As Long
Dim nDiff As Long
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
With Cells(i - 1, 1)
nDiff = .Offset(1, 0).Value - .Value
If nDiff > 1 Then
.Offset(1, 0).Resize(nDiff - 1).EntireRow.Insert
.AutoFill .Resize(nDiff), xlFillDays
End If
End With
Next i
End Sub

Jim
 
Back
Top