VLOOKUP and Wrap Text

[DK]

Hi,

I’m using a VLOOKUP to display text in different worksheets in the same
workbook.

The trouble I’m having is getting the data to display exactly as it does in
the reference worksheet.

Single cells with a single line of text show correctly, however, if the text
in the single cell is greater than one line or contains bulleted lines, then
the returns in the other worksheets either show a line of hash symbols
(##########) or just the first line of text.

I have used a General format for all the cells on all the worksheets and the
‘Wrap Text’ checkbox is ticked. I would therefore expect the row height of
the cells that contain the VLOOKUP formula to change so it displays all the
reference data but it doesn’t.

Will I have to preset the row heights on the other worksheets so that the
data displays correctly?

I’m using Excel 2k3

DK

 

[Dave Peterson]

The only time I've seen those ###### symbols with text values is when I have the
cell formatted as Text and the length of the text is between 256 and 1024
characters.

Did you convert your formulas to values and then format the cell as text? If
you did, try formatting those offending cells as General.

As for the rowheight stuff, excel won't change the rowheight based on the
results of a formula.

But you could use an event macro to do it for you:

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

 

[DK]

Dave,

Thanks for your help and prompt reply, I used the General format to get rid
of the hash symbols, but unfortunately the event macro doesn't quite fit the
bill so I'll just have to settle for predetermined row heights for now.

The macro condenses the row height to a single line rather than increase it
to fit the text. Kinda back to square one but never mind.

DK

 

[Dave Peterson]

The macro will work if you're not using merged cells.

 

[DK]

Ahhh, that might be the problem then. It will only be used on cells that are
merged.

 

[Dave Peterson]

Jim Rech wrote a macro called AutoFitMergedCellRowHeight that you may like:
http://groups.google.com/groups?threadm=e1$1uzL1BHA.1784@tkmsftngp05

Ahhh, that might be the problem then. It will only be used on cells that are
merged.