View Single Post
  #4   Report Post  
Old December 14th 04, 11:08 PM
Dave Peterson
Posts: n/a

If you format all those header cells, what do you see.

If you see those date serial numbers for some, but not others, then you haven't
quite fixed the problem.

Just reformatting the cell won't help. You have to reenter the value, too.

And maybe something like:

With ThisWorkbook.Sheets("Database1")
Set b = .Range("K4")
End With
With wb.Sheets("C Diagram")
Set a = .Range("B6")
End With
with b
.numberformat = "@" 'text
.value = a.text
.value = format(a,"mm.yyyy")
end with

When you're typing the contents of cells that you want treated as text, you can
either pre-format the cell as text ("@" in code) or prefix the entry with an
apostrophe (').

b.value = "'" & a.text
b.value = "'" & format(a,"mm.yyyy")

might work ok to (depending on what's in that "a" cell.)

Richard H wrote:


thank you for your response. I've investigated further and made
certain that all header values are formatted the same way now, like
this: m/yyyy.

But the problem didn't go away. What I've discovered is that the
collapse of the line diagram into one "column" happens when the more
than four data columns are added. It doesn't matter if the last one
is the column to the far right or far left or somewhere in between.
Everything is OK until the data series comprises more than four

I've also discovered that with a date formula like the one I'm using
now, the X axis "stretches" acroding to what dates I enter as
headers. If the first date is 1/1900 and the others are recent, the
first data points wind up at the extreme left of the diagram while
the others appear on top of each other at the extreme right. The
stacking of all points that I've described happens when the dates in
the header are all alike. So what I gather is that the date format is
creating the trouble, and that I should revert to text format to make
the X axis "static" again.

Now, the trouble is that when I import data into the five
workjsheets, the dates come over in date format ("dec.04" etc), even
though they're formatted as text in the original workbooks. I'm using
this VBA code to handle the import:

With ThisWorkbook.Sheets("Database1")
Set b = .Range("K4")
End With
With wb.Sheets("C Diagram")
Set a = .Range("B6")
End With
a.Formula = a.Value2
a.Copy b

So it seems that I need to amend the last two lines so that the value
is copied/pasted in text format.

Can this be done??


Dave Peterson wrote in

If you type 38292 in a cell and format it as a Date, you'll see
11/01/2004 (Nov 1, 2004).

I suspect that some of your header values are dates and some are

If you want to make them text, you can start with an apostrophe:

or preformat the cell as text


Dave Peterson