Breaking up the hi-lo series line?

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
0
jpkelly (10)
2/21/2004 10:22:22 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
544 Views

Similar Articles

[PageSpeed] 42

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

jpkelly@alltel.net (Jim Kelly) wrote in message news:<c9a37d50.0402211422.3bf144e4@posting.google.com>...
> 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
0
jpkelly (10)
2/22/2004 12:34:20 PM
Reply:

Similar Artilces:

Hi
Hi wollt nur mal sehen ob es geht.Mit meinem (outlook)windows E-Mail. On Wed, 4 Jul 2007 14:44:52 +0200, "Arvid Stumbitz" <abba12345678910@gmx.de> wrote: >Hi wollt nur mal sehen ob es geht.Mit meinem (outlook)windows E-Mail. Bitte frage in eine gruppe fuer Outlook. Diese gruppe est fuer Microsoft Access. Vielleicht microsoft.public.de.outlook ware am bestens. John W. Vinson [MVP] ...

I delete an email it remains in folder with line through it
How do I move deleted email from in box to deleted subfile without keeping a copy in the inbox with strikethru in it?? I have to email accounts on the computer one is pop3 the other is ms exchange? Not good with computers Are you sure its pop3 and exchange? it sounds like you set up an IMAP account - deleted items are marked with a strikethrough until you purge the folder from the edit menu. You can also use a view that hides items marked for deletion. We can be more specific if we knew the version of Outlook you use. -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? htt...

pivot from more than 65K lines: how?
What I am trying to do: have several excel files with data and want to consolidate in a single file What I did: tried to paste all the data in a single file and create a pivot from there... the problem is that I can not copy after reaching 65K lines.... Trying to create a pivot from *multiple* excel does not give me the possibility to put in the layout the fields I want (as if I was using a single sheet)... as it consolidates all the fields in the different tables... Any urgent help please?.. MANY TIA! luis Do you have Access? If so then import all your excel sheets into a singl...

RMA ( Credit) Missing Line Items
This is a multi-part message in MIME format. ------=_NextPart_000_0056_01C87CA9.5D5D6C20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Two RMA documents were received and then set as "Ready to Close". Both = documents show, in GP, as "History" but missing the line items. Checked the SQL tables and both RMA documents are in the "open" tables = (SVC05000 and SVC05200) and the line items are in the tables and appear = to be linked to the header. =20 How do we get the line items to appear on the RMA docu...

non-continuous section breaks
I’ve been doing a lot of content removal and rearrangement. When I view headers/footers in a Word 2007 .docm file, they number from beginning to end like this: Section 1, 2, 3, 4, 7, and 8. The fallout from this is the page numbering for section 7 will not continue from previous section. Is there a way to make the section numbers sequential again so my page numbers will correct themselves? Also, is this a cautionary tale about not cutting whole sections and moving them (I have a mix of landscape and portrait pages). I’ve never experienced non-sequential section numbering bef...

Bottom border line won't print in Word 2004 for Mac
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel On the screen I can place a page border in Word 2004 for Mac document, but in Print Preview the bottom line of the border disappears. And, of course, when I print, the bottom line won't appear. It doesn't appear that any footer (which I don't want) is in the way of the margin that I have set. Any solution to this? See: http://word.mvps.org/faqs/formatting/BottomsDontPrint.htm Also confirm that you have the latest printer driver from the mfr's web site as well as all current updates for Offi...

double lines
I am trying to type and address in a cell and can't get it to drop down to make another line, please help Use Alt-Enter instead of just Enter......... Vaya con Dios, Chuck, CABGx3 "WFDFirefighter" <WFDFirefighter@discussions.microsoft.com> wrote in message news:A657DEAE-5171-45AE-A57D-F8CE2DB5F122@microsoft.com... > I am trying to type and address in a cell and can't get it to drop down to > make another line, please help Hello- Chuck's reply is valid for creating a line break in a cell, so no challenge there. Just a suggestion, though - it sounds l...

XmlSerializer/XmlTextWriter all data on one line
I'm an XML newb. I'm serializing a class and when I inspect the xml file, all the data is on one line rather than being nested and indented Is that normal? <code> StreamWriter sw = new StreamWriter(m_optionsXmlFilePath, false); XmlSerializer xmlSerializer = new XmlSerializer(typeof(Options)); XmlTextWriter xmlTextWriter = new XmlTextWriter(sw); xmlSerializer.Serialize(xmlTextWriter, this); xmlTextWriter.Close(); </code> Hello! > I'm serializing a class and when I inspect the xml file, all the data is on > one line rather than being nested and indented > ...

xy scatter line goes below zero
The line drawn in my xy scatter chart goes below zero of the y-axis, is there any way I can stop this? My first 3 points are all zero and then there is a very steep increase, the line that connects them assumes that there is a dip into negative values before the increase when this is not actually the case. Hi, Have you got the Smooth line option ticked? Cheers Andy jon7 wrote: > The line drawn in my xy scatter chart goes below zero of the y-axis, is there > any way I can stop this? My first 3 points are all zero and then there is a > very steep increase, the line that connect...

replace manual page breaks WITH section breaks
Hello everyone -- There is no problem searching FOR section breaks and replacing them WITH something else, but there seems to be no way to do the reverse -- there is no Section Break choice in the Replace With pop-up window. Typing in ^b just produces an error. Is there a way to do this? Thank you all, -Lynne Please ignore question, folks. I just found a workaround here: http://support.microsoft.com/kb/136260 -Lynne Here it "Elessvie" wrote: > Hello everyone -- There is no problem searching FOR section breaks and > replacing them WITH somethin...

double lines and rounded corners on a solid box
How can I create a solid box with double lines and rounded corners in Publisher 2000? Thanks Anna You will have to use an AutoShape -- ...

Keeping Validation References When Breaking out Spreadsheets Using
Hello, I am using a version of Ron Debruin’s macro that breakouts spreadsheets into separate spreadsheets using a filter on a selected column. The issue that I am having is that I have a series of validation references located in the main sheet in hidden rows (rows 1-14 are hidden). I need to be able to retain these references in all the newly created sheets and retain the fixed references. How do I do this? Thanks in advance. Modified Ron Debruin Macro Sub FPR_Breakout_Worksheets() Dim calcmode As Long Dim ws1 As Worksheet Dim ws2 As Worksheet Dim WS...

Extracting name out of line
I am copying a line off the web with 4 names in it, separated by spaces (From 1 to 6 spaces). I am trying to extract name 3 from the line. Tried split(Var, " ") however Name moves according to the space between the names. Var(2) can be the Name or according to spaces between names, Var(6) Can you help me? Thank you, Gordon On 27 Jan 2007 15:37:39 -0800, "Gordon" <gwelch1938@yahoo.com> wrote: >I am copying a line off the web with 4 names in it, separated by >spaces (From 1 to 6 spaces). I am trying to extract name 3 from the >line. Tried split(V...

Line of Credit Account #2
This is driving me nuts. What is the best way to set up a Line of Credit (LOC) account? It is actually a $500 LOC account for my checking account for when my checking account dips below $0. Both the checking account and LOC accounts import separately into Money from my bank. Should I set up the LOC as an account, a liability, or as "other". I tried them but keep getting confused because depending on whether the LOC automatically deposited money into my checking account or I transferred money from my checking account to pay off my LOC, it will say either "From" o...

Inventory Site/LocationCode Order Lines on MSCRM1.2 to GPS8.0
We are currently running MSCRM 1.2 and GPS 8.0, with the MS Integration between them. Our Sales force uses various Inventory locations when placing orders within our GPS system. Because of business reasons we can't consolidate into a single Inventory Location as the Integration documentation suggests. The documentation allow says that it only supports the use of one Inventory location, which you set in the "Settings and Administration" tool. It appears that you can modify the BizTalk Mapping for the SalesOrderDetail channel to route a value from a custom field in MSCRM...

?-Change data series range as data is entered?
I have a chart which shows a prediction curve and an actual curve. The prediction curve runs out until the end of a project. The actual curve is populated as data is entered. I will be generating a number of these charts from a datatable. I would like to plot the prediction curve for the duration of the project (easy). I would then like to plot the actual curve with only the existing data and not have the line drop to the x-axis at the end. Can I put a formula in the data series "x values" field? I hope that my description makes sense..... Thanks, Carl -- isofuncurves ---...

CEditView No line wrap
How do I keep CEditView in my SDI application from wrapping the lines of text entered? i.e., I want the horizontal scrollbar. "Joe Estock" <jestock@NOSPAMnutextonline.com> wrote in message news:vrs98r4qk4so3f@corp.supernews.com... > How do I keep CEditView in my SDI application from wrapping the lines of > text entered? i.e., I want the horizontal scrollbar. Give it the (ES_AUTOHSCROLL|WS_HSCROLL) styles. In a CEditView derived class you can insure this by ORing the CREATESTRUCT.style member in an override of PreCreateWindow. -- Jeff Partch [VC++ MVP] ...

Changing series formula
Hello, Im using the WorksheetFunction.Substitute code to change a series formula. But when a series sources data which is #NA, the code wont work, as the data is not visible on the chart. Anyway around this to change those series also? many thanks ahead. Ali - Convert the series type to xlArea first, then you'll be able to access the series. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Ali wrote: > Hello, > > Im using the WorksheetFunction.Substitute code to change a series formula. But when a series sources ...

Pass command line file name parameter
Hi, I have an application with classes Myapp, MyappDoc and MyappView. The input filename from command line's parameter is stored as public variable sFilename in Myapp.h which is included in MyappDoc.cpp and MyappView.cpp. There're compiling errors: MyappDoc.cpp : error C2065: 'sFilename' : undeclared identifier MyappView.cpp : error C2065: 'sFilename' : undeclared identifier Why? If the input filename from command line's parameter is stored as public variable in MyappDoc.h, how to access it from Myapp.cpp? Thanks, ybc The other problem is, when I set the...

Small problem with a line chart
Hi I have a smal problem with a line chart. The chart shows progress of my teams league points over a season, the data is derived from a series of rows which I fill in after a game. In the data, I am using COUNTBLANK to have a blank cell until the row contains data EG =IF(COUNTBLANK(P53:Y53)>0,"",((P53+U53)*3)+Q53+V53) The problem is that the data series assumes unfilled rows (IE the future unplayed games) are zero, so there is a line that connects from the top of my line down to the zero on my X axis. I would just like the line to stop at the last value. Can anyone suggest a w...

X axis needed for Line Chart
-- FL ...

2D line shape
Hi, If I draw a line and draw another line from the end of the first line, the whole becomes instantly a 2D shape and the two lines themselves cannot be extended individually. I am having trouble with that. I often type in a cable number in a line and that number normally shows right on top of the line. Now if I type a number with the two lines I described above, the number appears in the middle of the new born 2D shape. Is there a way to change lines separately from each other after they have become a 2D shape ? Or is there a way to avoid them becoming a 2D shape ? Thanks, Arnold The ...

Hi Blinking/Flashing Text in cell based upon date value
Hi, I need a project completion sheet where .. the contents (TEXT) of the particular cell is to flash/blink upon some condition TRUE will any one respond pls Thank q, Syed Hi, Just a personal view but if I opened a workbook and it blinked at me I would close and delete it but if you must then have a look here. http://www.cpearson.com/excel/BlinkingText.aspx -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "EXCEL.SYED" wrote: > H...

Highlight ing of active line within excel
is it possible to when using the find option when locating something within a spreadsheet can the search resulting line can that be highlighted to provide easy reading Take a look at Chip Pearson's RowLiner: http://cpearson.com/excel/RowLiner.htm In article <40AD1702-43AB-48F4-9222-4D42C983B893@microsoft.com>, "glen2351" <glen2351@discussions.microsoft.com> wrote: > is it possible to when using the find option when locating something within a > spreadsheet can the search resulting line can that be highlighted to provide > easy reading ...

Urgent Help
All, Sorry but need some quick help in this project. I was told to create a quartile chart for last one year of closing price of Soybean Commodity. I am going to paste small data set here. Can someone please guide how to show quartiles as vertical lines in a chart? Also someone please suggest what should be X axis and Y axis values here? Date Close Price 1/1/2009 38.36 Quartile 1 60.7825 1/2/2009 43.4 Quartile 2 67.27 1/3/2009 45.34 Quartile 3 76.9675 1/4/2009 46.49 1/5/2009 49.12 1/6/2009 49.5 1/7/2009 50.5 1/8/2009 51.13 1/9/2009 51.8 1/10/2009 51.86 1/11/20...