"=" in text file formatting messes with my code loop- more elegant (and faster) solution?
 

I'm trying to get the following line of code to work;

mywrksht.Range(Usecol & CStr(rw)).Value = TotalDataArray(J, rw)

I'm parsing a text file and loading the data; it crashes on a value of "= =
="- three equals signs (although I added spaces between them here for
readability, I think they are sequential in the real file). Throughout the
file I'll get strings of various lengths of equal signs, depending on where
I'm parsing.

I can see why Excel doesn't like a cell starting with an equals sign when
the string doesn't work as a valid formula... on the other hand, I can't
control the formatting of the source document, so I'm stuck with what gets
pulled in. Throughout the document, rows of 'equals' symbols are used to
separate sections of the document. I can't throw a single apostrophe in
front of every incoming cell value, because I need many of the values as
numbers for later processing.

My less-elegant solution would be to check the Left(value, 1) to see if it
is an equals sign and add an apostrophe, but that adds a whole extra
calculation to each value I bring in. Is there a better way?

Thank you,
Keith

"=" in text file formatting messes with my code loop- more elegant (and faster) solution?
 

Did you format the cells as text? That seems to work.

Or if the array is a String rather than a Variant that seems to work with
General format.


--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"ker_01" wrote in message
...
I'm trying to get the following line of code to work;

mywrksht.Range(Usecol & CStr(rw)).Value = TotalDataArray(J, rw)

I'm parsing a text file and loading the data; it crashes on a value of "=
= ="- three equals signs (although I added spaces between them here for
readability, I think they are sequential in the real file). Throughout the
file I'll get strings of various lengths of equal signs, depending on
where I'm parsing.

I can see why Excel doesn't like a cell starting with an equals sign when
the string doesn't work as a valid formula... on the other hand, I can't
control the formatting of the source document, so I'm stuck with what gets
pulled in. Throughout the document, rows of 'equals' symbols are used to
separate sections of the document. I can't throw a single apostrophe in
front of every incoming cell value, because I need many of the values as
numbers for later processing.

My less-elegant solution would be to check the Left(value, 1) to see if it
is an equals sign and add an apostrophe, but that adds a whole extra
calculation to each value I bring in. Is there a better way?

Thank you,
Keith

"=" in text file formatting messes with my code loop- more elegant(and faster) solution?
 

Maybe you could force that cell to be text:

mywrksht.Range(Usecol & rw).Value = "'" & TotalDataArray(J, rw)

or

with mywrksht.Range(Usecol & rw)
.numberformat = "@" 'text
.Value = TotalDataArray(J, rw)
end with

======

I like this format
mywrksht.cells(rw,usecol)....
when I have a row and column.

ker_01 wrote:

I'm trying to get the following line of code to work;

mywrksht.Range(Usecol & CStr(rw)).Value = TotalDataArray(J, rw)

I'm parsing a text file and loading the data; it crashes on a value of "= =
="- three equals signs (although I added spaces between them here for
readability, I think they are sequential in the real file). Throughout the
file I'll get strings of various lengths of equal signs, depending on where
I'm parsing.

I can see why Excel doesn't like a cell starting with an equals sign when
the string doesn't work as a valid formula... on the other hand, I can't
control the formatting of the source document, so I'm stuck with what gets
pulled in. Throughout the document, rows of 'equals' symbols are used to
separate sections of the document. I can't throw a single apostrophe in
front of every incoming cell value, because I need many of the values as
numbers for later processing.

My less-elegant solution would be to check the Left(value, 1) to see if it
is an equals sign and add an apostrophe, but that adds a whole extra
calculation to each value I bring in. Is there a better way?

Thank you,
Keith

--

Dave Peterson

"=" in text file formatting messes with my code loop- more ele
 

If you don't like any of Tim's great suggestions, you're going to have to
check each data element either when you try to put it into a cell...
mywrksht.Range(Usecol & CStr(rw)).Value
or when you first put it into your array...
TotalDataArray(J, rw)
and add an apostrophe at the beginning...
'===
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown


"Tim Zych" wrote:

Did you format the cells as text? That seems to work.

Or if the array is a String rather than a Variant that seems to work with
General format.


--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"ker_01" wrote in message
...
I'm trying to get the following line of code to work;

mywrksht.Range(Usecol & CStr(rw)).Value = TotalDataArray(J, rw)

I'm parsing a text file and loading the data; it crashes on a value of "=
= ="- three equals signs (although I added spaces between them here for
readability, I think they are sequential in the real file). Throughout the
file I'll get strings of various lengths of equal signs, depending on
where I'm parsing.

I can see why Excel doesn't like a cell starting with an equals sign when
the string doesn't work as a valid formula... on the other hand, I can't
control the formatting of the source document, so I'm stuck with what gets
pulled in. Throughout the document, rows of 'equals' symbols are used to
separate sections of the document. I can't throw a single apostrophe in
front of every incoming cell value, because I need many of the values as
numbers for later processing.

My less-elegant solution would be to check the Left(value, 1) to see if it
is an equals sign and add an apostrophe, but that adds a whole extra
calculation to each value I bring in. Is there a better way?

Thank you,
Keith

"=" in text file formatting messes with my code loop- more elegant (and faster) solution?
 

Tim-

Thanks for the reply.

The problem is that the rest of the data file needs to be general format
(not forced to string, as the source file is something like (simplified):

==================
Title1 Title2 Title3 Title4
==================
13 19 4 17
15 0 0 14
12 16 7 28
==================
18 17 5 19
7 11 9 11
etc.

so I need to ignore the rows with the equals, and keep the rest as numbers
wherever possible (although the titles will default to text). In considering
my reply and how to explain the format, I've updated my code to check the
first character when loading each line (earlier in my code), so now my extra
calculations are down to one per row, rather than one per parsed value... a
30x improvement, although it still feels like an inelegant approach :)

Thanks again,
Keith

"Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message
...
Did you format the cells as text? That seems to work.

Or if the array is a String rather than a Variant that seems to work with
General format.


--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"ker_01" wrote in message
...
I'm trying to get the following line of code to work;

mywrksht.Range(Usecol & CStr(rw)).Value = TotalDataArray(J, rw)

I'm parsing a text file and loading the data; it crashes on a value of "=
= ="- three equals signs (although I added spaces between them here for
readability, I think they are sequential in the real file). Throughout
the file I'll get strings of various lengths of equal signs, depending on
where I'm parsing.

I can see why Excel doesn't like a cell starting with an equals sign when
the string doesn't work as a valid formula... on the other hand, I can't
control the formatting of the source document, so I'm stuck with what
gets pulled in. Throughout the document, rows of 'equals' symbols are
used to separate sections of the document. I can't throw a single
apostrophe in front of every incoming cell value, because I need many of
the values as numbers for later processing.

My less-elegant solution would be to check the Left(value, 1) to see if
it is an equals sign and add an apostrophe, but that adds a whole extra
calculation to each value I bring in. Is there a better way?

Thank you,
Keith

"=" in text file formatting messes with my code loop- more elegant (and faster) solution?
 

Thanks to Gary and Dave for your responses- they didnt show before I sent my
follow-up to Tim. Looks like lots of potential solutions.

I like this format
mywrksht.cells(rw,usecol)....

That looks simpler than what I always use...I'll give it a try!

Thanks,
Keith