vba line input not recognizing end of line

I had this problem, and saw others had it as well; I just wanted to
post my solution which turned out to be simple (in code).

For years I've been parsing a csv download file using the standard vba
line input function.  Well, the download file is still a csv, but
longer has CRLF, just LF.  As a result,  the line input function now
reads the entire file at once, instead of line by line.  To solve it,
I just used the replace function:

sub mproFileLineInput.

Open "c:\NeededFile.csv" For Input As #1     'Open download file

Line Input #1, txtline    'read line input, which reads entire file
because no CR Chr(13), just LF Chr(10)
NewText=Replace(txtline, Chr(10), vbCrLf)  'Find all Chr(10) LF and
replace with CRLR

Close #1

Open "c:\NeededFile.csv" For output As #1  'Open file again, but for
output this time
Print #1, NewTxtLine  'use print function to write contents, but with
LF replaced by CRLF
Close #1

End sub

Hope it helps anyone with same problem.
1
Beancounter
6/7/2010 4:17:32 PM
vb.general.discussion 1016 articles. 0 followers. Follow

15 Replies
12943 Views

Similar Articles

[PageSpeed] 19

"Beancounter" <jonescpa@gmail.com> skrev i meddelandet 
news:b9395c52-f604-4c9d-9ce4-f3e255ca7a45@d37g2000yqm.googlegroups.com...
>I had this problem, and saw others had it as well; I just wanted to
> post my solution which turned out to be simple (in code).
>
> For years I've been parsing a csv download file using the standard vba
> line input function.  Well, the download file is still a csv, but
> longer has CRLF, just LF.  As a result,  the line input function now
> reads the entire file at once, instead of line by line.  To solve it,
> I just used the replace function:
>
> sub mproFileLineInput.
>
> Open "c:\NeededFile.csv" For Input As #1     'Open download file
>
> Line Input #1, txtline    'read line input, which reads entire file
> because no CR Chr(13), just LF Chr(10)
> NewText=Replace(txtline, Chr(10), vbCrLf)  'Find all Chr(10) LF and
> replace with CRLR
>
> Close #1
>
> Open "c:\NeededFile.csv" For output As #1  'Open file again, but for
> output this time
> Print #1, NewTxtLine  'use print function to write contents, but with
> LF replaced by CRLF
> Close #1
>
> End sub
>
> Hope it helps anyone with same problem.

Just read here or there that someone deliberately saved files in that 
format. For me it's a big why?? to save some CR's? Or is it to ease for 
mac'ers to read the files?

Your example shows the outcome of same row-delimiter-change.

/Henning


0
Henning
6/7/2010 5:48:50 PM
"Henning" <computer_hero@coldmail.com> schrieb im Newsbeitrag 
news:hujbe3$bk8$1@news.eternal-september.org...
>
> Just read here or there that someone deliberately saved files in that format. 
> For me it's a big why?? to save some CR's? Or is it to ease for mac'ers to 
> read the files?
>
> Your example shows the outcome of same row-delimiter-change.
>
> /Henning
>


Henning,

a LF without a CR is UNIX, Linux, AmigaOS and now Mac -
since Mac OS X.
Until Mac OS version 9 the Mac was usung a single CR without
a LF. Same for the Apple II.
CR+LF is Windows, DOS, OS/2, CP/M, Atari TOS.

For the old teletypes you needed both CR + LF.
In the mid-seventies I saw a program using a teletype as "printer".
They used multiple CR without a LF to write columns.
With a teletype you had to send first the CR (carriage return) then
the LF (line feed) due to the latency time of the mechanical equipment.
If you did it the other way, the first character of the new line was
printed somewhere to the right, not in the leftmost position.

Helmut.


1
Helmut
6/7/2010 6:18:40 PM
Henning wrote:
....

> Just read here or there that someone deliberately saved files in that 
> format. For me it's a big why?? to save some CR's? Or is it to ease for 
> mac'ers to read the files?
> 
> Your example shows the outcome of same row-delimiter-change.
....

It's Unix-ish; I commented on the same posting not long ago that it's 
not wise to do so for Windows platforms in that there is much that doing 
so may unexpectedly break when the files are used for anything other 
than an app that knows to expect it.

--
0
dpb
6/7/2010 6:59:23 PM
Beancounter wrote:
> I had this problem, and saw others had it as well; I just wanted to
> post my solution which turned out to be simple (in code).
>
> For years I've been parsing a csv download file using the standard
> vba line input function.  Well, the download file is still a csv,
> but longer has CRLF, just LF.  As a result,  the line input
> function now reads the entire file at once, instead of line by
> line.  To solve it, I just used the replace function:
>
> sub mproFileLineInput.
>
> Open "c:\NeededFile.csv" For Input As #1     'Open download file
>
> Line Input #1, txtline    'read line input, which reads entire file
> because no CR Chr(13), just LF Chr(10)
> NewText=Replace(txtline, Chr(10), vbCrLf)  'Find all Chr(10) LF and
> replace with CRLR
>
> Close #1
>
> Open "c:\NeededFile.csv" For output As #1  'Open file again, but for
> output this time
> Print #1, NewTxtLine  'use print function to write contents, but
> with LF replaced by CRLF
> Close #1
>

FWIW, if you need to do this often or quickly, there's a faster way
(actually, a few faster ways).

The least faster, but most straightforward way, is (AIR CODE):

  Open "c:\NeededFile.csv" For Binary As #1

  Buf$ = String$(LOF(1), 0)
  Get 1,,Buf$
  Buf$ = Replace$(Buf$, vbLf, vbCr) ' NOT vbCrLf
  Put 1,1,Buf$
  Close #1

This works because while VB won't deal with files having only a LF
delimiter, it does just fine with only a CR delimiter (VB essentially
ignores LFs). It's faster because the string (and the file) doesn't
need to expand.

More speed if you avoid strings (and Unicode):

  Dim Idx As Long
  Dim Buf() As Byte

  Open "c:\NeededFile.csv" For Binary As #1


  Idx = LOF(0)
  ReDim Buf(1 To Idx) As Byte
  Get 1,,Buf()
  For Idx = 1 To Idx
    If Buf(Idx) = 10 Then
      Buf(Idx) = 13
    End If
  Next
  Put 1,1,Buf()
  Close #1

Of course any method, including the one you propose, should only be
used if you know that the file contains only LF delimiters.

-- 
   Jim Mack
   Twisted tees at http://www.cafepress.com/2050inc
   "We sew confusion"

0
Jim
6/7/2010 7:44:47 PM
"Henning" <computer_hero@coldmail.com> wrote:

>Just read here or there that someone deliberately saved files in that 
>format. For me it's a big why?? to save some CR's? Or is it to ease for 
>mac'ers to read the files?

The CRLF format is peculiar to DOS and Windows.  In the rest of the world it's 
just CR.  

0
sfdavidkaye2
6/7/2010 8:01:30 PM
sfdavidkaye2@yahoo.com (David Kaye) wrote:

>The CRLF format is peculiar to DOS and Windows.  In the rest of the world it's 
>just CR.  

I got it backwards.  It's LF.  

0
sfdavidkaye2
6/7/2010 8:02:49 PM
David Kaye wrote:
> "Henning" <computer_hero@coldmail.com> wrote:
> 
>> Just read here or there that someone deliberately saved files in that 
>> format. For me it's a big why?? to save some CR's? Or is it to ease for 
>> mac'ers to read the files?
> 
> The CRLF format is peculiar to DOS and Windows. In the rest of the
> world it's just CR.
> 

For suitable definitions of "rest" and time frame, anyway... :)

DEC RT-11 was at least one crlf that I recall.

CDC NOS used double 6-bit 0's (after 60-bit words).

Many other things that now seem oddities... :)

--
0
dpb
6/7/2010 8:30:01 PM
"David Kaye" <sfdavidkaye2@yahoo.com> wrote in message 
news:hujj6n$ac1$2@news.eternal-september.org...

>>Just read here or there that someone deliberately saved files in that
>>format. For me it's a big why?? to save some CR's? Or is it to ease for
>>mac'ers to read the files?
>
> The CRLF format is peculiar to DOS and Windows.

In the sense of they're the only surviving major OSes that use that 
convention. They certainly didn't invent it just to be different; they 
inherited it from their predecessors.

> In the rest of the world it's just CR.

You've already corrected yourself on that one, but realize that "the rest of 
the world" isn't as big as it might sound when you consider just how much of 
the world is DOS and Windows! 


0
Jeff
6/7/2010 10:13:27 PM
On 07/06/2010 21:01, David Kaye wrote:
> "Henning"<computer_hero@coldmail.com>  wrote:
>
>> Just read here or there that someone deliberately saved files in that
>> format. For me it's a big why?? to save some CR's? Or is it to ease for
>> mac'ers to read the files?
>
> The CRLF format is peculiar to DOS and Windows.  In the rest of the world it's
> just CR.

Erm, the vast majority of (text based) network protocols specify a CRLF 
line ending.
SMTP, POP, IMAP, HTTP (and derivatives), NNTP, Email content, etc. all 
use CRLF.

-- 
Dee Earley (dee.earley@icode.co.uk)
i-Catcher Development Team

iCode Systems

(Replies direct to my email address will be ignored.
Please reply to the group.)
0
Dee
6/8/2010 8:21:20 AM
"Jeff Johnson" <i.get@enough.spam> wrote in message 
news:hujqu9$v41$1@news.eternal-september.org...
> "David Kaye" <sfdavidkaye2@yahoo.com> wrote in message
>> The CRLF format is peculiar to DOS and Windows.
>
> In the sense of they're the only surviving major OSes that use
> that convention. They certainly didn't invent it just to be
> different; they inherited it from their predecessors.

.. . . who themselves inherited it from the old fashioned manual typewriter, 
although in the case of the manual typewriter it was LF followed by CR.

Mike




0
Mike
6/8/2010 8:54:26 AM
"Mike Williams" <Mike@WhiskeyAndCoke.com> schrieb im Newsbeitrag 
news:O$rSzguBLHA.980@TK2MSFTNGP04.phx.gbl...
> "Jeff Johnson" <i.get@enough.spam> wrote in message 
> news:hujqu9$v41$1@news.eternal-september.org...
>> "David Kaye" <sfdavidkaye2@yahoo.com> wrote in message
>>> The CRLF format is peculiar to DOS and Windows.
>>
>> In the sense of they're the only surviving major OSes that use
>> that convention. They certainly didn't invent it just to be
>> different; they inherited it from their predecessors.
>
> . . . who themselves inherited it from the old fashioned manual typewriter, 
> although in the case of the manual typewriter it was LF followed by CR.
>
> Mike
>


Mike,

I can't remember the sequence my old manual typewriter performed the tasks,
it  probably differed from manufacturer to manufacturer, and I never used an
electrical typewriter. I however used/programmed a typewriter in the
mid-seventies and while you could first punch  LF followed by CR it was
*not* advisable to do so. The first character on the new line wouldn't be in
the leftmost position due to the latency time of the mechanical equipment.
In other words, the next character was printed while the print head was still
moving to the left side.
They had deliberately shortened the delay after a CR to increase overall
print speed. If you used CR-LF this didn't matter, the line feed could
happen while the print head was still moving.

Helmut. 

0
Helmut
6/8/2010 11:34:05 AM
"Helmut Meukel" <Helmut_Meukel@NoProvider.de> wrote in message 
news:hul9re$4o7$1@news.eternal-september.org...

> Mike, I can't remember the sequence my old manual
> typewriter performed the tasks, it  probably differed
> from manufacturer to manufacturer

As far as I recall it was the same for every manufacturer, and with good 
reason. Both the line feed (the rotation of the roller around which the 
paper was fed) and the carriage return (the actual sliding of the entire 
carriage from left to right) were performed with just one lever. The user 
moved the lever and the first part of the lever movement caused the 
mechanism to first rotate the roller (line feed) and whilst it was doing 
that it did not meet with sufficent mechanical resistance to actually begin 
to move the carriage along. It was only after it has rotated the roller by 
the equivalent of one line (using the first small amount of the user's lever 
movement) that the lever came up against the mechanical roller stop, and the 
rest of the user's continued movement of the lever then caused to carriage 
to move along. This enabled the user to perform both a line feed and a 
carriage return (an often required action) with one single movement of the 
lever, and it also allowed the user to perform just a line feed without a 
carriage return (another often required action) simply by moving the same 
lever just a short amount, without continuing to move it once the "begin to 
move the carriage" resistance was felt (for line spacing between paragraphs 
etc and whenever else it was required to position the next line a number of 
lines below the preceeding one).

> I however used/programmed a typewriter in the
> mid-seventies and while you could first punch  LF
> followed by CR it was *not* advisable to do so.
> The first character on the new line wouldn't be in
> the leftmost position due to the latency time of the
> mechanical equipment.

Ah, but that's not what I would call a manual typewriter. It's one of those 
new fangled beasts with the fancy electric motors! And of course it 
certainly does not pre-date the manual typewriters that I am talking about 
;-)

Mike




0
Mike
6/8/2010 12:22:09 PM
On Tue, 8 Jun 2010 09:54:26 +0100, "Mike Williams"
<Mike@WhiskeyAndCoke.com> wrote:

>"Jeff Johnson" <i.get@enough.spam> wrote in message 
>news:hujqu9$v41$1@news.eternal-september.org...
>> "David Kaye" <sfdavidkaye2@yahoo.com> wrote in message
>>> The CRLF format is peculiar to DOS and Windows.
>>
>> In the sense of they're the only surviving major OSes that use
>> that convention. They certainly didn't invent it just to be
>> different; they inherited it from their predecessors.
>
>. . . who themselves inherited it from the old fashioned manual typewriter, 
>although in the case of the manual typewriter it was LF followed by CR.
>

It will perhaps make more sense if you appreciate that 'Line Printing"
came from Telegraphy/Teletype devices, not 'manual typewriters'.

Various conventions came about to abstract the notion of a "New Line".
Many of these conventions overloaded a specific value in order to save
clicks/space. So whenever one takes a closer look they have to
consider the context under which a "NewLine" is defined or expected.

For example, the reason Lf/Cr may occasionally fail under a particular
scheme - is because it is not recognized as a "NewLine" while Cr/Lf is
- even though literal control defined by each in whatever order
produces the same result.
0
ralph
6/8/2010 8:51:09 PM
On Jun 8, 7:22=A0am, "Mike Williams" <M...@WhiskeyAndCoke.com> wrote:
> "Helmut Meukel" <Helmut_Meu...@NoProvider.de> wrote in message
>
>
> > I however used/programmed a typewriter in the
> > mid-seventies and while you could first punch =A0LF
> > followed by CR it was *not* advisable to do so.
> > The first character on the new line wouldn't be in
> > the leftmost position due to the latency time of the
> > mechanical equipment.
>
> Ah, but that's not what I would call a manual typewriter. It's one of tho=
se
> new fangled beasts with the fancy electric motors! And of course it
> certainly does not pre-date the manual typewriters that I am talking abou=
t
> ;-)
>
> Mike

You're probably referring to a good old-fashioned Underwood
typewriter!  I used to use one of those!  Young kids these days and
their new-fangled electricity!

Chris
0
Chris
6/8/2010 9:18:05 PM
"ralph" <nt_consulting64@yahoo.net> wrote in message 
news:4n6t061c8sgjaesugfq6leh52gft2l26ch@4ax.com...
> On Tue, 8 Jun 2010 09:54:26 +0100, "Mike Williams"
> <Mike@WhiskeyAndCoke.com> wrote:
>>although in the case of the manual typewriter
>> it was LF followed by CR.
>
> It will perhaps make more sense if you appreciate that
> 'Line Printing" came from Telegraphy/Teletype devices,
> not 'manual typewriters'.

What do you mean by, "it will perhaps make more sense"? It already makes 
sense to me. Always has done. As I stated, and as you can see in the above 
extract, "in the case of the manual typewriter it was LF followed by CR".  I 
don't care where the phrase "Line Printing" came from (although I do know 
where it came from). I never mentioned that phrase since it was not relevant 
to what I was saying. I simply stated the truth, and that is the fact that 
on the old fashioned manual typewriter it was almost universally "line feed 
before carriage return". There have been a few "odd ball" machines, but in 
general the operator of almost all manual typewriters typed characters one 
by one and as she did so the carriage slowly moved to the left, until the 
desired end of the line was reached. Then the operator almost invariably 
performed a "line feed" to roll the paper down to the next line, followed by 
a carriage return to return the carriage back to the right side of the 
machine in order to start typing the next line, and those two operations 
were carried out in that specific order by the same one lever movement. And 
of course old fashioned manual typewriters, which as I have said almost 
invariably used line feed followed by carriage return, long predated your 
telegraphy and teletype / teleprinter device (which, incidentally, we had 
great fun with many years ago when I was in the Army, sending images of the 
Queen and other unsavoury characters as Ascii :-)

Mike





0
Mike
6/9/2010 4:21:34 PM
Reply:

Similar Artilces:

Line Chart #13
Hi, The following is my data and I need to chart this. Is it possible to have a range in one cell? Thanks for your help in advance Experience (Years) Large Medium Small 1 $90,000-$100,000 $75,000-90,000 $65,000-78,000 2 $92,000-100,000 $80,000-95,000 $75,000-85,000 3 $105,000-130,000 $90,000-100,000 $85,000-95,000 4 $117,000-150,000 $100,000-115,000 $90,000-110,000 5 $140,000-170,000 $115,000-130,000 $100,000+ 6 $140,000-190,000 $135,000+ $100,000+ 7 $170,000-250,000 $135,000+ $110,000+ ...

Fixed Assets across business lines
Hello everybody - Is it possible to spread fixed asset depreciation across multiple business lines? We have certain assets that need to be depreciated across multiple GL accounts..is this possible? The simplest solution would be to use an allocation account (probably a fixed allocation account) as the depreciation expense account. This would spread the depreciation based on the percentages in the allocation account to multiple accounts. Mark On Sep 27, 4:25=A0pm, modon...@knight.com <mich...@microsoftinstructor.com> wrote: > Hello everybody - > Is it possible ...

profile not recognized
When I open outlook it tells me I have no profile, even thou I did create one at control email. I can send and recieve email with outlook express no problem, but I cant access outlook to view my PDA sync info. Please help! ...

Microsoft Outlook -- Pass Line Variable to Microsoft Access
Okay...I tried the Microsoft Access main newsgroup and didn't get an answer to my question; hence, I'm asking the Outlook professionals. Here's my question.... Whenever a new e-mail message is received with a pre-defined subject line, I want a VBA script to pass line String variables (from the body of a new message) to a Microsoft Access function (module). If I can pass the line variables from Outlook to an Access function, I would be all set. However, I am not sure how to capture line variables of a new message in Outlook, and how to pass them to Access. Any help would be...

How to get Recurring Appointment dates with VBA?
I need to loop through all future Outlook appointment items and populate a table with the date and subject of each appointment. The problem is with recurring apointments - how do I get the recurring dates? If the appointment is NOT recurring, this works: Dim ola As Outlook.AppointmentItem For Each ola In olns.GetDefaultFolder(olFolderCalendar).Items If ola.Start > Date Then myDate = ola.Start mySubject = ola.Subject End If But the above code will not return recurring appointments. I've tried this, but no luck: If ola.IsRecurring Then Dim varItem A...

VBA Code to Run External File Commands....
Hello, I have a small VB Code that trying to do 2 different things, but both need to go outside of Excel. ( copy / paste ) 'Open "C:\myfile.PAS \\BLOCKP_5\WDDrop" For Output As #1 'Copy "C:\myfile.PAS \\BLOCKP_5\WDDrop" 'Paste "\\blockP_5\WDDrop" 'Close #1 Problem : I run a macro that takes the contents and creates a .PAS File, ( midified CSV ), that file is then copied to a Label Program ( Loftware ) and it prints out labels. Currently, I added a command button to click on it to run the macro, it copies file to the C:\ drive, then I mi...

Excel 2003 - VBA
Hi Guys: I have a spreadsheet where I am pulling Account data from several sources. One thing that I am looking for is to see if a given account has been on the same strategy for the duration of the period under test. Visually I would determine this by scanning a row of data to see that the strategy code (Alpha Code) was consistent throughout the period. Is there a quick way of looking at a range of cells and determining if they are all the same? The period could be several hundred cells long. Craig You can use a formula like: =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A...

Filter a report by start and end date
* I am VERY new to MS Access, please forgive me if I do not give the correct information.* I have a table with a date column along with other columns with my necessary information. I have created a Report that displays my information by month. My problem is that is displays every month that is in the table. How do I make the report allow me to just look at 1 month at a time. I think what I need to do is set up some kind of form with a filter that will let me enter a start and end date that will somehow link back to my date column in my table and then filter my report for me. I am complete...

Input from Access
Hi All, I have an excel spreadsheet linked to Access. I input some information in Access and it appears in the Excel ss. The problem is that the numbers are formatted as text. This produces an error because the numbers are in a summed column. In Excel the column is formatted as number. Any ideas on how I can force the text to be formatted as number when it receives new data. The Excel Form is not open when updated by Access. Any help greatly appreciated, Jeff How are you transferring the data? Jeff wrote: > Hi All, > > I have an excel spreadsheet linked to Access. > &g...

Format all contacts with specific telephone input for Outlook and Motorola A1000
Some of my contacts from Outlook 2002 have entries like this: +44 (20) 12345678 and some have: +44-20-12345678 How can I make *all* my contacts on my A1000 have the telephone numbers in this format: [INDENT]+cc-ac-nnnnnnn[/INDENT] where *cc* is country code *ac* is area code and *nnnnnnn* is the telephone number. I would like them in that format with the "+" at the front (int'l format) and "-" seperating the numbers. I *don't* won't any spaces or parentheses in the numbers. It seems when I sync with the phone and Outlook, I get different entries - some with...

"This workbook has lost its VBA Project"
Receiving the above message when opening Excel2003, blank workbook. Tools -> MACRO everything disabled except for security which is set to medium... I have installed EVERYTHING run from computer, including common VB support. HELP! Carl And you get this when you click on the New icon on the standard toolbar? If yes, do you have a workbook named book.xlt in your XLStart folder? That's a template file that excel will use when you create a new workbook. If that template file had macros, maybe it got corrupted and excel is having trouble with it. In fact, if you have a book.xlt in y...

how to add overlay line chart to stacked bar
I'm using Microsoft Excel for Mac OS X. I'm trying to create an overlay chart with a stacked bar with a line over it. I can only get it to do a regular bar chart with a line over it. What am I doing wrong? Thanks! I no longer have a Mac, but you can try the following: 1. Create a stacked column chart from all the data 2. Select the series that you want as a line 3. Choose Chart>Chart Type, and choose the Line chart type, and one of the subtypes 4. Click OK Molly M wrote: > I'm using Microsoft Excel for Mac OS X. I'm trying to create an overlay > chart with ...

How to stop the End User License Agreement popping up?
"log in as system administrator" means what? I turn the thing on, I don't have to log in. I'm the owner! In simple terms now, why do the End User License Agreements pop up every time the program is opened? this occurs for Picture Manager and Outlook and others. How do I permanently stop this happening? Probably better ask in the appropriate Windows newsgroup. "blackheath" <blackheath@discussions.microsoft.com> wrote in message news:7ABBA253-9D6A-4340-AC36-EA30BFC43CAD@microsoft.com... > "log in as system administrator" means what? > I turn ...

tangent line #2
how can i draw a tangent line on a graph with a curve of best fit? On Tue, 18 Sep 2007, in microsoft.public.excel.charting, mark <mark@discussions.microsoft.com> said: >how can i draw a tangent line on a graph with a curve of best fit? Use algebra in the spreadsheet to construct both the curve and the tangent, then use an XY (Scatter) Chart type to present them on a graph. Don't use the built-in Trend Line facility in charts, because then you won't be able to work out for yourself what the tangent is. -- Del Cotter NB Personal replies to this post will send email to de...

Ending Balance Problems
I've been having serious problems trying to have my account information show correctly in MS Money 2006. I'm doing a fresh install and create a new setup. I log onto my Bank's websites and download a history of transactions in MS Money format. After following prompts, the accounts are created and transactions imported. I cannot for the life of me get the ending balance to show correctly. In microsoft.public.money, Brian wrote: >I've been having serious problems trying to have my account information show >correctly in MS Money 2006. > >I'm doing a fresh...

Try catch finally is not recognized by powershell
Hi, I am trying to execute a script in Windows 2003 box. I wrote a sample program to test try-catch-finally test. But it throws error saying finally is not supported in this version of the language. Sample program i wrote is below ----------------------- Trycatch.ps1 ----------------------- # Create some explicity typed values [int] $i = 123 [string] $s = "Some string" [object] $o = $s # Now try to convert an object into an integer (which will fail) try { # Invalid conversion; o contains a string not an int $i = [int] $o; }# catch the error an...

VBA Conditional formatting #2
Yes, but can I instead of the color also get the conditions? Thanks Steve -- Steven E ----------------------------------------------------------------------- Steven E.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1512 View this thread: http://www.excelforum.com/showthread.php?threadid=26812 Take a look at the code at Chip Pearson's site: http://www.cpearson.com/excel/CFColors.htm "Steven E." wrote: > > Yes, but can I instead of the color also get > the conditions? > > Thanks > Steven > > -- > Steven E. > --...

VBA
Hi, I am trying to find a way of determining whether variables (in a large array of 35000 data points, defined as Single) are actually blank or are really zero. I appreciate that numeric variables default to 0. In my case, I dimension my large array as single, then set it equal to a range. The range will contain empty cells (perhaps up to half the time), signifying missing data, but may also contain data values that are 0. I want to distinguish between these possibilities. I appreciate that I could declare the array as variant or string, then test to see if individual elements are blan...

Label an average line in a dynamic chart
I have a chart (xy scatter) showing the last 52 weeks of data (it's accumulated weekly dosage of a medicine - several years worth of data), i.e. total dose per week plotted against the dates. I also have an average line (Series 2) charted - it's the average of the 52 y-values. I did it by creating some 'dummy' values in the data sheet, looking something like this: E F 1 12/06/2004 1.798076923 2 11/06/2005 1.798076923 Then the line is constructed from =SERIES("Average",Data!$E$1:$E$2,Data!$F$1:$F$2,2) As a final finishing touch I'd like to have a l...

command line switch to open a specific worksheet?
Is there a way to open a specific worksheet in a workbook using command line switches? I'm trying to create a menu selection in AutoCAD that will open an Excel workbook to a specific worksheet inside of that workbook. Typing the command "start" <CR> followed by "Excel /filename/" <CR> will open that particular file, but how do I get it to open to a specific worksheet? Thanks for any help, Paul There's no command line switch that will activate a worksheet. You have to do one of the following: -Save the workbook with the desired sheet active. Exc...

trying to get sheet 1 to recognize and move student names to pg 2
Sheet 1 contains students names in column A in ascending order, columns b thru k are columns which grading numbers of either 1 point or 2 points, column l is the total points of colums b thru k. Sheet 2 contains 5 columns , column a is 17 to 20 points, column b is 13 to 16 points, column c is 9 to 12 points, 5 to 8 points, column d is 5 to 8 points and column e is 0 to 4 points. I need sheet 1 column L to recognize sheet 2 and move the students name from column A in that row to the graph on sheet 2 according to the point system Is this one-time operation? You have only 5 c...

Confidence Interval with VBA
what is the VBA code that will compute the condfidence interval(lower and upper thanks A confidence interval for what? Jerry rick wrote: > what is the VBA code that will compute the condfidence interval(lower and upper) > thanks ...

Advanced Filter VBA Help
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C4AE8E.E5D5F550 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, Currently trying to copy data from one sheet to another. All data in = column "J" is seperated and a new sheet is added for every unique value = in column "J". Right now, only the values of the corresponding rows of = "J" are copied, but I need the formulas and formatting to be copied as = well. Can anyone give me a heads up on how to do this? Thanks so much Sub E...

Multiple subtotals on a single line
I have a large file that has all of the drugs that our patients have taken for a period of time. I need to get a subtotal by the drug number (average cost) and the number of clients that have received the drugs by drug number. Is there a way to get both the subtotals on a single line. I need to transfer the data from this worksheet to another sheet provided by contractor. The file looks similar to this. NDC# (drug ID) Client# Unit Cost Date (asst' other columns) You want to treat the drugid and client# as one field (essentially)? How about using a helper column: =a2&"...

Deleting Multiple lines
Hi I have a spreadsheet which lists all invoices that are due to be included in a weeks payment run. Is there anyway i can set up the spreadsheet to delete multiple lines for each supplier so all i am left with is a list of all suppliers i am going to make a payment to that week. Thanks Two ways spring to mind that could help. i) Create a Pivot Table - In the Data menu there is a wizard - Highlight the range where the data is and follow the steps. On completion you will select the values as the data range suppliers as Rows and invoice date as column heading. This will sum the totals ...