Auto-resize of merged cells 1

[datadan]

I have a table on sheet one with numbers 1-11 in column A and text in column B. The text in column B is sometimes 1 line, it maybe 20 lines of text (all within the same cell of column B).

Sheet 2 is a data entry sheet. Depending on a value (from 1 to 11) input by the user, I do a vlookup to grab the corresponding text, and then put the text into a cell on sheet 3.

Here is the problem: I can't get the text to size correctly in its new home on sheet three. I have tried merging several cells, but then I only get the first line of text. If I don't merge the cells, All the text is crammed into the first cell. It spreads down vertically instead of across. I can fix things by manually adjusting the row width, but I would rather an programtic solution as I am trying to develop and data entry and print out sheet.

Thanks in advance for your help.

 

[xlbo]

I think the only way round this is to count the number of characters in yopur string using LEN
Decide how wide your column is going to be and set a constant for dividing
ie if you want 40 characters per line use
NoOfRows = INT(Len(testStr)/40)+1
Then change the row height accordingly ie if NoOfRows = 5 then change the row height to be 5* normal

or, you could try adding line breaks
testStr = 'Set reference to string for testing here
newStr = ""
For i = 1 To Len(testStr)
If i Mod 10 = 1 And i <> 1 Then
newStr = newStr & Chr(10) & Mid(testStr, i, 1)
Else
newStr = newStr & Mid(testStr, i, 1)
End If
Next i
MsgBox newStr
'Or enter into the cell

HTH
~Geoff~

 

[flashb]

why not try a simple macro one entry is complete which autofits the data in column B of sheet 3;

Columns(&quot;B:B&quot.EntireColumn.AutoFit

AshB

 

[xlbo]

'Cos if you need to find out how many lines the text needs to go onto - that's the crux of the problem - you could autofit but you won't know how wide the cell'l be.

Note re: last post - the code will do 10 chars per line
change If i Mod 10 = 1 And i <> 1 Then
to If i Mod 40 = 1 And i <> 1 Then for 40 chars


HTH
~Geoff~

 

[datadan]

xlbo,

I have an equation that determines what my cell heigth must be. I have a macro that sizes the cell:

Sheets(&quot;test_summary&quot.Select
ActiveCell.Rows(&quot;1:1&quot.EntireRow.Select
Selection.RowHeight = 132

What is my syntax to replace 132 with a value from sheet2 cell Z3 (within the macro)?

Thanks

 

[xlbo]

Hi DataDan - try this
With sheets(&quot;Test_Summary&quot
.Rows(&quot;1:1&quot.rowheight = sheets(&quot;Sheet2&quot.range(&quot;Z3&quot.value
end with

HTH
~Geoff~

 

[datadan]

Bravo,

Thank you all who replied (Flahsb for the macro idea and xlbo for the nitty gritty.)